Help with a #Value! issue

[hardworkingprofessional84]

Hello Friends~

With my Excel sheet I am trying to get rid of a #Value! error I am getting if there is no data in previous cells. Any ideas how to take care of this?

=IF(E15/-$C$35>0.8,"5",IF(E15/-$C$35>0.6,"4",IF(E15/-$C$35>0.4,"3",IF(E15/-$C$35>0.2,"2",IF(E15/-$C$35>0.0000000000001,"1","")))))

That is the formula...and it leaves the #Value! in other cells.

Thanks for the help in advance

 

[hardworkingprofessional84]

Shouldn't the false portion of the statement fix that with the final little piece having "" in it? I am really unsure now why it isn't working.

 

[hardworkingprofessional84]

Ok, I've figured out one way to fix the issue. I went under Tools--->options and under the "View" tab, I unchecked Zero Values. I then entered a zero into the entire column. This column was the basis for which the rest of the formulas were based.

Example: E4= C4*D4. C4 also plays a role in other formulas such as C4*G4.

By entering a 0 in C4---it removes all of the #Value! errors I was receiving.

Although a cleaner solution would be more ideal.

 

[xenou]

You can wrap the formula in an If():

=IF(OR(SUM(E15), SUM(C35)=0),"", ,IF(E15/-$C$35>0.8,"5",IF(E15/-$C$35>0.6,"4",IF(E15/-$C$35>0.4,"3",IF(E15/-$C$35>0.2,"2",IF(E15/-$C$35>0.0000000000001,"1",""))))),"")

Or (AlphaFrog's formula):
=IF(OR(SUM(E15), SUM(C35)),"", LOOKUP(E15/-$C$35 +0.0000000000001, {0,0.2,0.4,0.6,0.8},{1,2,3,4,5}))

The SUM() will test if cells have zero or if they contain text (sum evaluates to zero for text).

-----------------
Note:
You can use custom formatting or conditional formatting to hide zeros (without changing the application settings).

Also, E4 * C4 will be the same if C4 has a zero or is blank (but not if it is a "" value). Multiplication and addition will treat empty cells as zero.

ξ

 

[jmthompson]

Try

=IFERROR(IF(E15/-$C$35>0.8,"5",IF(E15/-$C$35>0.6,"4",IF(E15/-$C$35>0.4,"3",IF(E15/-$C$35>0.2,"2",IF(E15/-$C$35>0.0000000000001,"1",""))))),"")

 

[hardworkingprofessional84]

I'm having trouble getting either of those formulas to work. Thank you though!

 

[AlphaFrog]

This uses your original formula but first tests if E15/-$C$35 produces any error.

=[COLOR="Red"]IF(ISERROR(E15/-$C$35),"",[/COLOR]
  IF(E15/-$C$35>0.8,"5",IF(E15/-$C$35>0.6,"4",
  IF(E15/-$C$35>0.4,"3",IF(E15/-$C$35>0.2,"2",
  IF(E15/-$C$35>0.0000000000001,"1","")))))[COLOR="Red"])[/COLOR]