Skip to content
BOL Conferences
Learn More - Click Here!

Page 1 of 3 1 2 3
Thread Options
#1716601 - 07/03/12 01:40 PM EXCEL formula help
KTMiteComply Offline
Power Poster
KTMiteComply
Joined: Jul 2007
Posts: 3,298
only if I want to....
Good Morning everybody...hope everybody is doing well! I miss seeing you guys!

I need some excel help please. I have the following formula posted to help me with these certain colums...here is the formula:

=IF(F1288<D1288,F1288/D1288,F1288/E1288)


(d column) $92,995.12 (e column)-$1,815.84 (f column) ($94,810.96) (answer) -101.95%

This all worked out perfectly until I came to this:

(d column) $124,868.60 (e column) $149,268.60 (f column) $24,400.00 (answer) 19.54%

The answer should be 16.35, so somehow I need a "but if" scenario. In this reasoning, I would need column F to be divided by column e...so really the above formula would work if it read like this:

=IF(F1288>D1288,F1288/D1288,F1288/E1288)

but I do not want to have to check everytime to see which columns I need to change the greater than/less than sign...I want it to read that if column f is greater than d, then do the above divison, however if column F is less than D, then use the above division. How do I string these two above "IF" arugments together?

Thanks so much for any thoughts or help!!!

Happy 4th everybody! smile
KT
_________________________
Trust in the Lord with ALL your heart...Prov 3:5-6

Return to Top
Chat! - BOL Watercooler
#1716606 - 07/03/12 01:45 PM Re: EXCEL formula help KTMiteComply
A_G Offline
10K Club
Joined: Jul 2004
Posts: 18,989
Hi, KT! grin
_________________________
With the lights out, it's less dangerous.

Return to Top
#1716607 - 07/03/12 01:46 PM Re: EXCEL formula help KTMiteComply
KTMiteComply Offline
Power Poster
KTMiteComply
Joined: Jul 2007
Posts: 3,298
only if I want to....
Hi handsome smile
_________________________
Trust in the Lord with ALL your heart...Prov 3:5-6

Return to Top
#1716610 - 07/03/12 01:47 PM Re: EXCEL formula help KTMiteComply
A_G Offline
10K Club
Joined: Jul 2004
Posts: 18,989
blush
_________________________
With the lights out, it's less dangerous.

Return to Top
#1716633 - 07/03/12 02:10 PM Re: EXCEL formula help KTMiteComply
doobydoobydoo Offline
Power Poster
doobydoobydoo
Joined: May 2007
Posts: 4,195
Basking in the Cool Weather
Youre not simply chaning the greater than less than, the if statement handles that

If greater than, then do this, if not then do this....


Im not sure I understand your request.
_________________________
I'll be in the hospital bar.
Uh, you know there isn't a hospital bar, Mother.
Well, this is why people hate hospitals.

Return to Top
#1716637 - 07/03/12 02:11 PM Re: EXCEL formula help KTMiteComply
Bobby Boucher Offline
Power Poster
Bobby Boucher
Joined: Aug 2006
Posts: 6,577
Down Yonder
Originally Posted By: KTMiteComply
Good Morning everybody...hope everybody is doing well! I miss seeing you guys!

I need some excel help please. I have the following formula posted to help me with these certain colums...here is the formula:

=IF(F1288<D1288,F1288/D1288,F1288/E1288)


(d column) $92,995.12 (e column)-$1,815.84 (f column) ($94,810.96) (answer) -101.95%

This all worked out perfectly until I came to this:

(d column) $124,868.60 (e column) $149,268.60 (f column) $24,400.00 (answer) 19.54%

The answer should be 16.35, so somehow I need a "but if" scenario. In this reasoning, I would need column F to be divided by column e...so really the above formula would work if it read like this:

=IF(F1288>D1288,F1288/D1288,F1288/E1288)

but I do not want to have to check everytime to see which columns I need to change the greater than/less than sign...I want it to read that if column f is greater than d, then do the above divison, however if column F is less than D, then use the above division. How do I string these two above "IF" arugments together?

Thanks so much for any thoughts or help!!!

Happy 4th everybody! smile
KT



Try this:

=IF(F1288<0,(IF(F1288<D1288,F1288/D1288,F1288/E1288)),(IF(F1288>D1288,F1288/D1288,F1288/E1288)))
_________________________
...not only will I do it for you, I... I... I... yes, yes, I'll do it for you.

Return to Top
#1716642 - 07/03/12 02:15 PM Re: EXCEL formula help KTMiteComply
doobydoobydoo Offline
Power Poster
doobydoobydoo
Joined: May 2007
Posts: 4,195
Basking in the Cool Weather
just duplicated your work and got -5221% for the first example and 19.54 for the second example.... not sure if you've got an error or i do
_________________________
I'll be in the hospital bar.
Uh, you know there isn't a hospital bar, Mother.
Well, this is why people hate hospitals.

Return to Top
#1716646 - 07/03/12 02:17 PM Re: EXCEL formula help KTMiteComply
doobydoobydoo Offline
Power Poster
doobydoobydoo
Joined: May 2007
Posts: 4,195
Basking in the Cool Weather
Originally Posted By: KTMiteComply

=IF(F1288>D1288,F1288/D1288,F1288/E1288)





Using this formula alone I get the Results that you are looking for.... although my first number isnt negative
_________________________
I'll be in the hospital bar.
Uh, you know there isn't a hospital bar, Mother.
Well, this is why people hate hospitals.

Return to Top
#1716651 - 07/03/12 02:21 PM Re: EXCEL formula help KTMiteComply
HappyGilmore Offline
10K Club
Joined: Jun 2004
Posts: 19,844
Pulling people out of the ditc...
I'd suggest hiring an analyst to perform this type of work...
_________________________
Providing alternative truths since the invention of time

Return to Top
#1716653 - 07/03/12 02:22 PM Re: EXCEL formula help KTMiteComply
NotALawyer Offline
Gold Star
NotALawyer
Joined: Nov 2001
Posts: 455
Originally Posted By: KTMiteComply


(d column) $92,995.12 (e column)-$1,815.84 (f column) ($94,810.96) (answer) -101.95%



Is the 94,810.96 a negative or positive?

(edited for spelling...it's still early)
Last edited by NotALawyer; 07/03/12 02:22 PM.
Return to Top
#1716657 - 07/03/12 02:23 PM Re: EXCEL formula help KTMiteComply
NotALawyer Offline
Gold Star
NotALawyer
Joined: Nov 2001
Posts: 455
What are the additional criteria you are using to get to the 16.35%? There doesn't appear to be enough data in the three columns to empirically determine whether to use < or > as the starting point for the formula.

Return to Top
#1716658 - 07/03/12 02:23 PM Re: EXCEL formula help KTMiteComply
doobydoobydoo Offline
Power Poster
doobydoobydoo
Joined: May 2007
Posts: 4,195
Basking in the Cool Weather
ahhhhh - missed that - inconsistent formatting
_________________________
I'll be in the hospital bar.
Uh, you know there isn't a hospital bar, Mother.
Well, this is why people hate hospitals.

Return to Top
#1716661 - 07/03/12 02:25 PM Re: EXCEL formula help KTMiteComply
Bobby Boucher Offline
Power Poster
Bobby Boucher
Joined: Aug 2006
Posts: 6,577
Down Yonder
Originally Posted By: Bobby Boucher
Try this:

=IF(F1288<0,(IF(F1288<D1288,F1288/D1288,F1288/E1288)),(IF(F1288>D1288,F1288/D1288,F1288/E1288)))

Assuming that F1288=E1288-D1288, this would also work:

=IF(E1288<D1288,(IF(F1288<D1288,F1288/D1288,F1288/E1288)),(IF(F1288>D1288,F1288/D1288,F1288/E1288)))
_________________________
...not only will I do it for you, I... I... I... yes, yes, I'll do it for you.

Return to Top
#1716690 - 07/03/12 02:45 PM Re: EXCEL formula help KTMiteComply
NotPerfect Offline
Platinum Poster
Joined: Jan 2009
Posts: 682
Midwest
^^^BOL Geek Squad

Return to Top
#1716691 - 07/03/12 02:45 PM Re: EXCEL formula help Bobby Boucher
KTMiteComply Offline
Power Poster
KTMiteComply
Joined: Jul 2007
Posts: 3,298
only if I want to....
Originally Posted By: Bobby Boucher
Originally Posted By: KTMiteComply
Good Morning everybody...hope everybody is doing well! I miss seeing you guys!

I need some excel help please. I have the following formula posted to help me with these certain colums...here is the formula:

=IF(F1288<D1288,F1288/D1288,F1288/E1288)


(d column) $92,995.12 (e column)-$1,815.84 (f column) ($94,810.96) (answer) -101.95%

This all worked out perfectly until I came to this:

(d column) $124,868.60 (e column) $149,268.60 (f column) $24,400.00 (answer) 19.54%

The answer should be 16.35, so somehow I need a "but if" scenario. In this reasoning, I would need column F to be divided by column e...so really the above formula would work if it read like this:

=IF(F1288>D1288,F1288/D1288,F1288/E1288)

but I do not want to have to check everytime to see which columns I need to change the greater than/less than sign...I want it to read that if column f is greater than d, then do the above divison, however if column F is less than D, then use the above division. How do I string these two above "IF" arugments together?

Thanks so much for any thoughts or help!!!

Happy 4th everybody! smile
KT



Try this:

=IF(F1288<0,(IF(F1288<D1288,F1288/D1288,F1288/E1288)),(IF(F1288>D1288,F1288/D1288,F1288/E1288)))


Holy smokes...that worked!!!!!!! I wish I could totally understand the reasoning of how you made this work...it was hard enough for me to figure out just one "IF" stmt, must less read how you came up with this formula!

THANKS A MILLION...and thanks everybody for your responses to help me!!! Have a wonderful day! smile
_________________________
Trust in the Lord with ALL your heart...Prov 3:5-6

Return to Top
#1716696 - 07/03/12 02:47 PM Re: EXCEL formula help Bobby Boucher
KTMiteComply Offline
Power Poster
KTMiteComply
Joined: Jul 2007
Posts: 3,298
only if I want to....
Originally Posted By: Bobby Boucher
Originally Posted By: Bobby Boucher
Try this:

=IF(F1288<0,(IF(F1288<D1288,F1288/D1288,F1288/E1288)),(IF(F1288>D1288,F1288/D1288,F1288/E1288)))

Assuming that F1288=E1288-D1288, this would also work:

=IF(E1288<D1288,(IF(F1288<D1288,F1288/D1288,F1288/E1288)),(IF(F1288>D1288,F1288/D1288,F1288/E1288)))


Your assumption would be totally correct....ugggh....I wish I could understand this. I love working with Excel want to take an advanced course b/c I think I've learned about all I can learn through the years on my own! crazy
_________________________
Trust in the Lord with ALL your heart...Prov 3:5-6

Return to Top
#1716697 - 07/03/12 02:51 PM Re: EXCEL formula help KTMiteComply
Bobby Boucher Offline
Power Poster
Bobby Boucher
Joined: Aug 2006
Posts: 6,577
Down Yonder
Originally Posted By: KTMiteComply
Your assumption would be totally correct....ugggh....I wish I could understand this. I love working with Excel want to take an advanced course b/c I think I've learned about all I can learn through the years on my own! crazy

Actually, I made it much more difficult than necessary. I leaped at trying to get the right result but wasn't really paying attention to what you needed. I think what you're wanting to key on is whether column F is negative or positive.

To make it simpler, try:

=IF(F1288<0,F1288/D1288,F1288/E1288)

_________________________
...not only will I do it for you, I... I... I... yes, yes, I'll do it for you.

Return to Top
#1716700 - 07/03/12 02:55 PM Re: EXCEL formula help Bobby Boucher
Peepers Offline
10K Club
Joined: Jul 2002
Posts: 13,994
Originally Posted By: Bobby Boucher
Assuming


never assume, it makes a
Click to reveal..
moleass
out of you.............and someone else, I'm sure
_________________________
blah

Return to Top
#1716701 - 07/03/12 02:56 PM Re: EXCEL formula help KTMiteComply
Matt_B Offline
Diamond Poster
Matt_B
Joined: Sep 2011
Posts: 1,648
A CU, Where Regs Don't Apply
It always helps me to reason out in words exactly what I want a forumula to accomplish first. Especially when you're getting into a nested if/ifelse type statement. It's amazing the stuff you can do with formulas, if you understand how to make it all work smile
_________________________
Someone's about to get horned!

Return to Top
#1716707 - 07/03/12 03:02 PM Re: EXCEL formula help Bobby Boucher
Bobby Boucher Offline
Power Poster
Bobby Boucher
Joined: Aug 2006
Posts: 6,577
Down Yonder
Originally Posted By: Bobby Boucher
Originally Posted By: KTMiteComply
Your assumption would be totally correct....ugggh....I wish I could understand this. I love working with Excel want to take an advanced course b/c I think I've learned about all I can learn through the years on my own! crazy

Actually, I made it much more difficult than necessary. I leaped at trying to get the right result but wasn't really paying attention to what you needed. I think what you're wanting to key on is whether column F is negative or positive.

To make it simpler, try:

=IF(F1288<0,F1288/D1288,F1288/E1288)


Or if you're keying on whether column E is negative, use:

=IF(E1288<0,F1288/D1288,F1288/E1288)

To determine whether you're keying on E or F, assume that D=-1000, E=-500, and F=500. Are you wanting your answer to be -100% or -50%?
_________________________
...not only will I do it for you, I... I... I... yes, yes, I'll do it for you.

Return to Top
#1716709 - 07/03/12 03:04 PM Re: EXCEL formula help Bobby Boucher
KTMiteComply Offline
Power Poster
KTMiteComply
Joined: Jul 2007
Posts: 3,298
only if I want to....
Originally Posted By: Bobby Boucher
Originally Posted By: KTMiteComply
Good Morning everybody...hope everybody is doing well! I miss seeing you guys!

I need some excel help please. I have the following formula posted to help me with these certain colums...here is the formula:

=IF(F1288<D1288,F1288/D1288,F1288/E1288)


(d column) $92,995.12 (e column)-$1,815.84 (f column) ($94,810.96) (answer) -101.95%

This all worked out perfectly until I came to this:

(d column) $124,868.60 (e column) $149,268.60 (f column) $24,400.00 (answer) 19.54%

The answer should be 16.35, so somehow I need a "but if" scenario. In this reasoning, I would need column F to be divided by column e...so really the above formula would work if it read like this:

=IF(F1288>D1288,F1288/D1288,F1288/E1288)

but I do not want to have to check everytime to see which columns I need to change the greater than/less than sign...I want it to read that if column f is greater than d, then do the above divison, however if column F is less than D, then use the above division. How do I string these two above "IF" arugments together?

Thanks so much for any thoughts or help!!!

Happy 4th everybody! smile
KT



Try this:

=IF(F1288<0,(IF(F1288<D1288,F1288/D1288,F1288/E1288)),(IF(F1288>D1288,F1288/D1288,F1288/E1288)))


Ok...it all worked until I got to this

(column d) $57.33 (Column e)$10,646.67 Column (f) $10,589.34 (answer) 18470.85%

Answer should be 99.4 So if I change your formula to read this BB then it works...so does that mean I need to add another string to my formula...good grief, this is turning into a nightmare for me. I agree with Happy... sick

=IF(F1363>0,(IF(F1363<D1363,F1363/D1363,F1363/E1363)),(IF(F1363>D1363,F1363/D1363,F1363/E1363)))
_________________________
Trust in the Lord with ALL your heart...Prov 3:5-6

Return to Top
#1716712 - 07/03/12 03:08 PM Re: EXCEL formula help KTMiteComply
Peepers Offline
10K Club
Joined: Jul 2002
Posts: 13,994
there are a lot of F bombs being dropped in here
_________________________
blah

Return to Top
#1716715 - 07/03/12 03:10 PM Re: EXCEL formula help Bobby Boucher
KTMiteComply Offline
Power Poster
KTMiteComply
Joined: Jul 2007
Posts: 3,298
only if I want to....
Originally Posted By: Bobby Boucher
Originally Posted By: Bobby Boucher
Originally Posted By: KTMiteComply
Your assumption would be totally correct....ugggh....I wish I could understand this. I love working with Excel want to take an advanced course b/c I think I've learned about all I can learn through the years on my own! crazy

Actually, I made it much more difficult than necessary. I leaped at trying to get the right result but wasn't really paying attention to what you needed. I think what you're wanting to key on is whether column F is negative or positive.

To make it simpler, try:

=IF(F1288<0,F1288/D1288,F1288/E1288)


Or if you're keying on whether column E is negative, use:

=IF(E1288<0,F1288/D1288,F1288/E1288)

To determine whether you're keying on E or F, assume that D=-1000, E=-500, and F=500. Are you wanting your answer to be -100% or -50%?


BB...this formula seemed to correct my issue I just stated above.

the answer to your question on the scenario you gave would be -100%

Thanks for all your help...I do appreciate it!
_________________________
Trust in the Lord with ALL your heart...Prov 3:5-6

Return to Top
#1716716 - 07/03/12 03:11 PM Re: EXCEL formula help KTMiteComply
Peepers Offline
10K Club
Joined: Jul 2002
Posts: 13,994
twas no problem
_________________________
blah

Return to Top
#1716717 - 07/03/12 03:13 PM Re: EXCEL formula help KTMiteComply
Pale Rider Offline
10K Club
Pale Rider
Joined: Aug 2002
Posts: 34,318
under the Lone Star
Hello KT, thank you for the inspirational posts on FB!
_________________________
Societies that do not find work in and of itself "pleasing to God and requisite to Man," tend to be highly corrupt.


Return to Top
Page 1 of 3 1 2 3