IF statement help Excel 2010

[Closed]
Report
Posts
4
Registration date
Wednesday November 23, 2011
Status
Member
Last seen
November 28, 2011
-
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
-
Hello,

This forum seems very helpful... I'm working on a spread sheet and have run into a wall with a specific IF statement.

I want the cells in one column to look at the data in other cells and then act accordingly e.g. when one cell in a column is "A" then it should multiply the data of two specified cells, when that cell contains "B", then it should multiply data of two other cells, and lastly sometimes it must look for more than one option e.g. "A","B","C", and then multiply accordingly.

I have entered the following formula, but keep getting an error message.

=IF(E5="A",G5*Q5,IF(E5="B",H5*Q5,IF(AND(E5="B",I5>0),O5*Q5,IF(E5="C",O5*Q5,IF(AND(F5="tD",F5="E",F5="F"),M5*Q5,IF(E5="G",P5*Q5,,,))))))

Any help would be greatly appreciated!
Thanks




2 replies

Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
Hi BasilBloo,

Noticed the following in your formula:

1. AND(F5="tD",F5="E",F5="F")
F5 can never have 3 value's at once and therefore the result will always be false.

2. IF(E5="G",P5*Q5,,,)
Too many comma's. If you want to input nothing when statement is false then use: "". As in IF(E5="G",P5*Q5,"")

Point 1 is just for your information.
Point 2 will withhold you from confirming your formula, since the syntaxis isn't followed correctly.

Best regards,
Trowa
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
4
Registration date
Wednesday November 23, 2011
Status
Member
Last seen
November 28, 2011

TO: Trowa

Thank you for the help. My big mistake was point 1. Figured it out with trial and error.

Regards,
BasilBloo
Posts
4
Registration date
Wednesday November 23, 2011
Status
Member
Last seen
November 28, 2011

Hi Trowa,

When entered into seperate cells in diffirent columns the formulae works, but as soon as I combine them, the last two statements fail...
I must be cancelling one statement with another - please help!

=IF(H5>0,H5*Q5,IF(OR(H5>0,O5>0,),O5*Q5,IF(E5="A",G5*Q5,IF(OR(H5="",J5>0),O5*Q5,IF(OR(J5="",M5>0),M5*Q5,IF(E5="B",P5*Q5,""))))))

Thank you!
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
Sorry for the late response BasilBloo.

The only things I noticed in your formula is that your first OR is unnecessary. If H5 isn't bigger then 0 then it won't be the second time around.

Your first OR also contains a spare comma at the end.

These points can be ignored and don't determine/change the outcome.

I have tested your formula by going through each logistical to see if it yields the desired result.
And........ it does for me.

Now I'm wondering what you mean by "the last two statements fail".
Do you mean you got an error message or are you not getting the desired result.

Best regards,
Trowa