Formula evaluation

[Edward]

Can someone review the following formula and tell me why I am getting the
#Value error?

=IF(Spacg>8,(FULL/VLOOKUP(TYPE,ProductData,6,FALSE)/((Spacg/8)))+(BBGROUT+BEAM*EA*QTY)+(((IF(ISBLANK(LINTEL),HALF*Setup!$C$15,HALF*Setup!$C$17))+(Setup!$C$19*CORNERS/2))/VLOOKUP(TYPE,ProductData,6,FALSE)),BBGROUT),IF(Spacg<16,(LENGTH*HEIGHT*QTY*EA*1.125)/VLOOKUP(TYPE,ProductData,6,FALSE))

The problem seems to be in the 2nd part of the formula from
IF(Spacg<16,(LENGTH*HEIGHT*QTY*EA*1.125)/VLOOKUP(TYPE,ProductData,6,FALSE)).
The spacg>8 represents numbers 16, 24, 32, 48, etc so if the spacg is 16, 24,
32, etc I want it to perform the 1st part of the function. If the spacg is 8,
I want it to perform the 2nd part of the function. I hope this isn't
confusing.

Thanks in advance

Ed

 

[T. Valko]

#VALUE! is usually the result if you're trying to do math operations on
TEXT.

The problem seems to be in the 2nd part of the formula from
IF(Spacg<16,(LENGTH*HEIGHT*QTY*EA*1.125)/VLOOKUP(TYPE,ProductData,6,FALSE)).

If the first part of the formula works, IF(Spacg>8, then you can eliminate
the lookup as a possible cause since it's common to both IFs. So, your
problem is somewhere in here:

(LENGTH*HEIGHT*QTY*EA*1.125)

At least one of those named cells must have a text entry.

 

[Dave]

Hi,
You can try selecting parts of the formula in the formula bar, and pressing
F9, which evaluates the selection.
You have to select a piece of the formula that can stand on its own. eg, you
could select:
VLOOKUP(TYPE,ProductData,6,FALSE)
or
Spacg>8
or
BBGROUT+BEAM*EA*QTY
or
HALF*Setup!$C$15,HALF*Setup!$C$17
or
IF(ISBLANK(LINTEL),HALF*Setup!$C$15,HALF*Setup!$C$17))
or
Setup!$C$19*CORNERS/2)

Each of these should return a value or a True/False. If any of them returns
the Value error, then the problem is contained within that selection.

Hope this helps
Regards - Dave.