n/a in one row of data is screwing up my total formula

[rayted]

Hi guys

Please see data below:

ROW 17 £234.82 0
ROW 18 N/A #N/A


roRow 17 has: =(E17+G17+I17)*0.1207 in one cell and =SUM(E17+G17+I17) in another cell

Row 18 = has 0 data (i.e N/A) so I don't know if i can write an N/A formula i.e. the above but change 17 to 18. Because there is no data to calculate (N/A), it is returning 0 and it is affecting my formula at the bototm of the sheet which calculates a running total.

Can anyone advise/

thank you!

[63falcondude]

Try

=SUM(IFNA(E17,0),IFNA(G17,0),IFNA(I17,0))*0.1207

and

=SUM(IFNA(E17,0),IFNA(G17,0),IFNA(I17,0))

[rayted]

Try

=SUM(IFNA(E17,0),IFNA(G17,0),IFNA(I17,0))*0.1207

and

=SUM(IFNA(E17,0),IFNA(G17,0),IFNA(I17,0))

Hi

thank you for your reply.

This did not seem to work. Please find my spreadsheet attached. You can see my formulas and the issue is everyhwere in the spreadsheet where #VALUE! is apparent.

So i guess if there is #VALUE! can excel exclude this or intrepret is a 0 and do the formula anyway? thank you!

thank you for helping...

[FDibbins]

What's causing the NA? Your file doesnt show. Consider fixing the NA at source, to eliminate the problem there?

[Saqib Qureshi]

Use this formula in N6

Please Login or Register  to view this content.

Press Cntl+Shift+Enter

[FDibbins]

No, I meant what is causing the NA at source?

Please upload a more detailed sample of your WB

(Thanks for the rep, too)