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]

Any quick solutions? I don't know why but Excel 2003 seems to be extremely buggy.

 

[mr.paul.c]

I assume by "no data" that the cells are blank?

This could potentially make things worse, but try putting a 0 in the blank cells and see if that makes a difference

 

[MrKowz]

Try this:

=LOOKUP((E15/-$C$35),{0,0.00000000001,0.20000000001,0.400000000001,0.600000000001,0.800000000001},{"",1,2,3,4,5})

 

[AlphaFrog]

Not sure what you mean by "Previous cells".

This checks if E15 and C35 both have values. It also uses the LOOKUP function instead of nested IFs.

=IF(OR(E15="", $C$35=""),"", LOOKUP(E15/-$C$35 +0.0000000000001, {0,0.2,0.4,0.6,0.8},{1,2,3,4,5}))

 

[mr.paul.c]

As far as the #VALUE! error itself is concerned, after looking it up, it is an error that occurs when a formula in a cell refers to different data types, an example being referencing 2 cells in a formula that contain text and numbers.

if you have in Cell A1 "cats" and A2 "25" for example, and in A3 you have the formula =A1*A2 it's gonna throw an error because you can't do that multiplication.

So if the other solutions don't work, it might be a good idea to go back through and double check that the cell references are correct

 

[hardworkingprofessional84]

here is a picture of what it looks like:

 

[hardworkingprofessional84]

The above picture has the formula in view.

 

[mr.paul.c]

I'm pretty sure that the problem is with your formula, but as I'm about to finish work I can't really spend time on looking at it properly, if I get chance I'll have a look when I get home

 

[MrKowz]

Have you tried the solution by AlphaFrog? Your error is resulting from the formula having nothing to calculate.