Getting #DIV/0! error when I am NOT dividing by zero. Anyone know why?

[9tanstaafl9]

I've been stumped for 3 days now and am resorting to begging for help.

This EXACT same formula (put there with a filldown) works perfectly for most rows, but on some it generates the Div/0 error and I don't know why.

Here is my formula:

=IF(ISERROR(VLOOKUP($A119,'Input Estimate'!A:F,6)/((VLOOKUP($A119,'Input Estimate'!A:C,3)/DollarsPerHour)/HoursPerDay)),0,VLOOKUP($A119,'Input Estimate'!A:F,6)/((VLOOKUP($A119,'Input Estimate'!A:C,3)/DollarsPerHour)/HoursPerDay))

Which I simplified to this in order to see the Div/0 error:

=VLOOKUP($A119,'Input Estimate'!A:F,6)/((VLOOKUP($A119,'Input Estimate'!A:C,3)/DollarsPerHour)/HoursPerDay)

The cells the formula is referencing in columns F and C have values. Those values are based on formulas, but I tried just typing in hard values and setting the calculation to automatic and it didn't help. I also tried changing the value in A119 to be the same as one of the earlier rows that did work, and that did make the formula work just fine.

A119 = 3020.000 (shows up in formula bar as 3020 and is formatted as a number on both sheets and there are no duplicates. Could the lack of zeroes be a problem for the vlookup?

Dollars per hour is currently set to 33, and hours per day is set to 8.

I just don't know what else to try.



ANY ADVICE on how to troubleshoot this issue would be greatly appreciated. I can't post the spreadsheet as it is all secret stuff that I'd be in big trouble for sharing.

 

[V_Malkoti]

What does this return?
=VLOOKUP($A119,'Input Estimate'!A:C,3)

[FONT=Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif]Its probably VLOOKUP which is returning 0.[/FONT]

 

[9tanstaafl9]

Yes, you are correct. I tried it for the other column as well and that is a zero. But why? There are numbers there.

I tried clicking where the formula is, entering an equal sign and then clicking on the appropriate row in column F and got the correct value.

 

[9tanstaafl9]

Ah crap. I'm an idiot.

My table was no longer sorted at that point in the macro and I hadn't noticed. Thank you for your insight. I hadn't thought to test just the vlookup before.

Duh!

Need emoticon for slapping palm against head.

 

[Michael M]

Ha, we all do it sometimes......maybe this emoticon would do for you.....

 

[9tanstaafl9]

Perfect. Saved it for later. Much later I hope. Thanks.

I wish lookups would simply error out if the data is not sorted. It is very misleading to just sort of work.