Previous Topic Next Topic

IF statement help Excel 2010

Closed
BasilBloo Posts 4 Registration date Wednesday November 23, 2011 Status Member Last seen November 28, 2011 - Nov 23, 2011 at 04:08 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Dec 8, 2011 at 09:48 AM
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




Related:

2 responses

Answer 1 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Nov 24, 2011 at 09:53 AM
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
Answer 2 / 2
BasilBloo Posts 4 Registration date Wednesday November 23, 2011 Status Member Last seen November 28, 2011
Nov 25, 2011 at 07:31 AM
TO: Trowa

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

Regards,
BasilBloo
0
BasilBloo Posts 4 Registration date Wednesday November 23, 2011 Status Member Last seen November 28, 2011
Nov 28, 2011 at 02:30 AM
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!
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Dec 8, 2011 at 09:48 AM
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
0