Thread Options
|
#1716601 - 07/03/12 01:40 PM
EXCEL formula help
|
Power Poster
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! KT
_________________________
Trust in the Lord with ALL your heart...Prov 3:5-6
|
Return to Top
|
|
|
|
#1716606 - 07/03/12 01:45 PM
Re: EXCEL formula help
KTMiteComply
|
10K Club
Joined: Jul 2004
Posts: 18,989
|
Hi, KT!
_________________________
With the lights out, it's less dangerous.
|
Return to Top
|
|
|
|
#1716610 - 07/03/12 01:47 PM
Re: EXCEL formula help
KTMiteComply
|
10K Club
Joined: Jul 2004
Posts: 18,989
|
_________________________
With the lights out, it's less dangerous.
|
Return to Top
|
|
|
|
#1716633 - 07/03/12 02:10 PM
Re: EXCEL formula help
KTMiteComply
|
Power Poster
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
|
Power Poster
Joined: Aug 2006
Posts: 6,577
Down Yonder
|
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! 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
|
Power Poster
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
|
Power Poster
Joined: May 2007
Posts: 4,195
Basking in the Cool Weather
|
=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
|
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
|
Gold Star
Joined: Nov 2001
Posts: 455
|
(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
|
|
|
|
#1716658 - 07/03/12 02:23 PM
Re: EXCEL formula help
KTMiteComply
|
Power Poster
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
|
Power Poster
Joined: Aug 2006
Posts: 6,577
Down Yonder
|
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
|
|
|
|
#1716691 - 07/03/12 02:45 PM
Re: EXCEL formula help
Bobby Boucher
|
Power Poster
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! 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!
_________________________
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
|
Power Poster
Joined: Jul 2007
Posts: 3,298
only if I want to....
|
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!
_________________________
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
|
Power Poster
Joined: Aug 2006
Posts: 6,577
Down Yonder
|
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! 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
|
10K Club
Joined: Jul 2002
Posts: 13,994
|
never assume, it makes a out of you.............and someone else, I'm sure
_________________________
blah
|
Return to Top
|
|
|
|
#1716707 - 07/03/12 03:02 PM
Re: EXCEL formula help
Bobby Boucher
|
Power Poster
Joined: Aug 2006
Posts: 6,577
Down Yonder
|
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! 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
|
Power Poster
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! 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... =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
|
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
|
Power Poster
Joined: Jul 2007
Posts: 3,298
only if I want to....
|
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! 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
|
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
|
10K Club
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
|
|
|
|
|
|