Previous Topic Next Topic

IF statement help Excel 2010

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

Réponse 1 / 2
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
24 Nov 2011 à 09:53
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
Réponse 2 / 2
BasilBloo Posts 4 Registration date Wednesday 23 November 2011 Status Member Last seen 28 November 2011
25 Nov 2011 à 07:31
TO: Trowa

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

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

Similar discussions

Excel - IF function with multiple words
Nick -
user -

14 responses