Previous Topic Next Topic

Adding two non-adjacent cells (number and text)

[Solved/Closed]
Report
Posts
24
Registration date
Monday November 2, 2015
Status
Member
Last seen
December 4, 2015
 -
Posts
24
Registration date
Monday November 2, 2015
Status
Member
Last seen
December 4, 2015
 -
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 reply

Answer 1 / 1
Posts
1004
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2020
 137
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)
View all 7 comments
Report
Posts
24
Registration date
Monday November 2, 2015
Status
Member
Last seen
December 4, 2015
 >
Posts
1004
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2020
yes
Report
Posts
1004
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2020
 137
Yes - I just tried it and it is definitively the adding 2 words together that is causing the error.
Report
Posts
24
Registration date
Monday November 2, 2015
Status
Member
Last seen
December 4, 2015
 >
Posts
1004
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2020
how can i have the formula to treat the any words or blank cells as zero(0)
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768 >
Posts
24
Registration date
Monday November 2, 2015
Status
Member
Last seen
December 4, 2015
how about some thing like this
=IF(OR('PILOT Data'!C341="",ISTEXT('PILOT Data'!C341)),0,'PILOT Data'!C341)+IF(OR('PILOT Data'!C346="",ISTEXT('PILOT Data'!C346)),0,'PILOT Data'!C346)
Report
Posts
24
Registration date
Monday November 2, 2015
Status
Member
Last seen
December 4, 2015
 >
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
Thank you that actually worked very well

Again thank you
Ask a question