Solved/Closed
dkayeh1 Posts 24 Registration date Monday November 2, 2015 Status Member Last seen December 4, 2015 - Nov 12, 2015 at 12:16 PM
dkayeh1 Posts 24 Registration date Monday November 2, 2015 Status Member Last seen December 4, 2015 - Nov 13, 2015 at 10:08 AM
Hello, I seem to be having a problem with a nested IF(statement) I am on a separate worksheet that I use to calculate my formulas for my summary page.

Here is a copy of the formula I used and one cell(B341) has a number in it and the other cell(B346) as the word na; the formulas gives me a #VALUE! error. I also have two cells that have N/A(C341) and n/a(C346) in them and it gives the same #VALUE! error. D341 has a number in it and D346 has a number in it and the formula works great. Here is the formula can anyone tell me what I am doing wrong. I had it combined but still got the error #VALUE! so I broke it down to this and I still get the same error message. So I am frustrated with it and need expert eyes to see what is wrong.

=IF(AND('PILOT Data'!C341="",'PILOT Data'!C346=""),0,IF(AND('PILOT Data'!C341="N/A",'PILOT Data'!C346="N/A"),0,IF(AND('PILOT Data'!C341="n/a",'PILOT Data'!C346="n/a"),0,IF(AND('PILOT Data'!C341="na",'PILOT Data'!C346="na"),0,IF(AND('PILOT Data'!C341="NA",'PILOT Data'!C346="NA"),0,IF(AND('PILOT Data'!C341>=0,'PILOT Data'!C346>=0),'PILOT Data'!C341+'PILOT Data'!C346,IF('PILOT Data'!C341="",0+'PILOT Data'!C346,IF('PILOT Data'!C346="",0+'PILOT Data'!C341))))))))

Thank you for your help on this formula

## 1 response

BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Nov 12, 2015 at 04:51 PM
Hi dkayeh1,

Im not an Excel expert, but to me it seems that you are treating words as if they were numbers in the final bit of your code. The final bit of code I am talking about is ...

`...  ('PILOT Data'!C341>=0,'PILOT Data'!C346>=0),'PILOT Data'!C341+'PILOT Data'!C346,IF('PILOT Data'!C341="",0+'PILOT Data'!C346,IF('PILOT Data'!C346="",0+'PILOT Data'!C341))))))))`

It looks to me as though you are adding "na" to "n/a" (or something like that). Have you forgotten to make the cells B341 and B346 at this point?

I hope this is a good enough explanation of what I mean ...

Also, as far as I can remember you are only allowed 7 nested "IF" statements. Im not sure if it counts the first "IF" in that 7, but it might be worth looking at. I see 8 "IF"s in your expression.

Good Luck.
Please let us know how you get on.

I really appreciate thank you messages as a payment for solving issues :o)
dkayeh1 Posts 24 Registration date Monday November 2, 2015 Status Member Last seen December 4, 2015
Nov 12, 2015 at 05:02 PM
Thank you for your help BrianGreen

Since this was just frustrating to me I went ahead and set a data validation for the cells and made it to where people inputting data had to put a number in the cell or leave it blank and then I just used a simple sum formula

Again thank you for your help
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Nov 12, 2015 at 05:05 PM
Does the whole expression work when just numbers are inserted into the cells?
dkayeh1 Posts 24 Registration date Monday November 2, 2015 Status Member Last seen December 4, 2015 > BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021
Nov 12, 2015 at 05:16 PM
yes
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Nov 12, 2015 at 05:28 PM
Yes - I just tried it and it is definitively the adding 2 words together that is causing the error.
dkayeh1 Posts 24 Registration date Monday November 2, 2015 Status Member Last seen December 4, 2015 > BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021
Nov 12, 2015 at 05:49 PM
how can i have the formula to treat the any words or blank cells as zero(0)