IF formula to include multiple conditions

Having problems a formula when cells have "0" value

=IF(C6=0,"",C6+$C$3/E6)

I need the formula to still calculate if C6 is 0 and if E6 is 0
also if possible I would like to suppress the #div/0 error

Originally Posted by nflsales

=if(c6=0,"",c6+iferror($c$3/e6,0))

The formula partially worked If e6 value is 0 it works, but if the c6 value is 0 the cell is left blank.
I tried a few things , but no success, little confused why the c6 is causung problems.

Much thanks in advance

Originally Posted by vlady

Yes the issue is C6 since if C6 is 0 then "" or null/blank
based on nflsales you can change the "" to 0

=if(c6=0,0,c6+iferror($c$3/e6,0))
When will it return blank?

This formula also partially worked If e6 value is 0 it works great, but if the c6 value is 0 and e6 has a value it results in 0.00

not sure why that is...

I would start with the E6=0 issue. Try this: =IF(E6=0,"",IF(C6=0,"",C6+$C$3/E6))

Good luck,
Jim Robison

If we still calculate if C6=0

=if(e6=0,"",c6+$c$3/E6)

Originally Posted by JohnTopley

If we still calculate if C6=0

=if(e6=0,"",c6+$c$3/E6)

This formula resolved the issue when C6 value is 0, but when E6 value is 0 and c6 has a value it returns a
error "#value!" It helped the C6 issue, but caused the e6 error.

Check what is in C3 - is it non-numeric? I have just checked the conditions above and it worked OK. If E6=0 and C6 has a value, it returns a blank.

Post a sample sheet.

Originally Posted by JohnTopley

Check what is in C3 - is it non-numeric? I have just checked the conditions above and it worked OK. If E6=0 and C6 has a value, it returns a blank.

Post a sample sheet.

For me if C6 is 0 it works, but if E6 is 0 I get #Value! error

Also note that E6 value comes from another eqaution =IF(D6=0,"",60/D6)

I atttached the excel file.

Quote Sheet-test.xlsx

Change formula in E6 and copy down:

=IF(D6=0,0,60/D6)

Originally Posted by JohnTopley

Change formula in E6 and copy down:

=IF(D6=0,0,60/D6)

This didn't work, I changed the forumla in E6 and get a Blank result in F6 when either c6 or e6 value is "0"

Your conditions are:

in E6: if(D6=0,0,60/D6)

in F6: IF (E6=0,0,C6+$c$3/60)

C6 and D6 are data only: C6 is only used in the formula in F6. If C6 is 0 but D6<> 0 then F6 will contain result.

If D6=0 then E6=0 then F6=0

So are the IF statements correct?

Originally Posted by JohnTopley

Your conditions are:

in E6: if(D6=0,0,60/D6)

in F6: IF (E6=0,0,C6+$c$3/60)

C6 and D6 are data only: C6 is only used in the formula in F6. If C6 is 0 but D6<> 0 then F6 will contain result.

If D6=0 then E6=0 then F6=0

So are the IF statements correct?

C6 is direct data only and is only added to the result of E6

D6 is direct data and used in the E6 Formula

E6 is the result of the data in D6 and is always the result 60/D6

so C6 can be 0 sometimes and D6 can be 0 which will drive E6 to be 0

I can not get the forulama to give a result when one of either C6 or E6 is at 0

Very confusing...

If E6 has no bearing on F6 why are we testing if it is 0 in F6 formula???

D6 is the problem: if 0 , all results 0. So is allowing D6=0 an error?

Value in C6 has no impact on F6 i.e. will not result in an error.

C6 is direct data only and is only added to the result of F6

Cell F6 formula: =IF(C6=0,$C$3/E6,IF(E6=0,"",C6+IFERROR($C$3/E6,0)))

This gives me the best result, but still have the #VALUE! error when both C6 & E6 are both Blank
I would love to remove the error if possible?

Current working formulas:

Cell F6: =IF(C6=0,$C$3/E6,IF(E6=0,"",C6+IFERROR($C$3/E6,0)))

Cell E6: =IF(D6=0,"",60/D6)



C6: direct data only

D6: direct data only

quote-sheet-test3.xlsx

Look at sheet TEST in attached - hopefully what you require.

Originally Posted by JohnTopley

Look at sheet TEST in attached - hopefully what you require.

Thank you very much John! Perfect.
I really appreciate everyone's time and efforts

Just glad we found a solution !!!!