IF Formula Returning with the #VALUE! Error

[garrett.grillo]

Hi Gang,

I’m creating a new project cash flow report for my company and I’m having trouble in cell S13 (see attached/linked excel workbook below) with an IF formula returning with the #VALUE! error and I believe the error is partially due to the IF formula trying to read a cell with another formula.

I need cell S13 and actually that whole S column to look at the related cells K13, N13, and U13 to when K13 is blank the S13 IF formula returns with a blank, so I do have that working perfectly.

But, when K13 contains a number at the same time when P13 does not, S13 returns with #VALUE!

What I need S13 to do is return with the sum (addition) between N13 and U13. If U13 contains a number and N13 is blank, then S13 adds the two cells together to return the numeric answer. If U13 does not contain a number and N13 is blank, then S13 again adds the two cells together which in this case would equal zero (0) and if the answer is zero (0), the formula will return with a blank cell and not the number zero (0).

Please, I need the help as it’s already 2:30am here in the office, lol.

#5 NG Line Replacement - Project Schedule and Cash Flow Forecast.xlsx

Thank you,

Garrett

[Hawkeye16]

It is throwing an error trying to add blank cells. If you want the blanks to come through as zero you can either put an if statement to say if "" then 0.

For cell S13:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

A simpler way, but prone to showing 0 when there is ANY error not just one from the blank cell addition is changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Personally I would use the first approach.

[Saarang84]

For colored cells from K7 onwards, use

Please Login or Register  to view this content.

and copy this to other colored cells

For other uncolored cells from K12 onwards, use

Please Login or Register  to view this content.

and copy this to other uncolored cells.

Refer attached workbook with corrected formulas.

[AlKey]

Your formulas in R13 and S13 can actually be condensed to =IFERROR(S13/$K13,"") And =IFERROR(N13+U13,"") respectively. The IFERROR function will work for any instances.