Formula evaluation
 

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

=IF(Spacg8,(FULL/VLOOKUP(TYPE,ProductData,6,FALSE)/((Spacg/8)))+(BBGROUT+BEAM*EA*QTY)+(((IF(ISBLANK(LINTEL),H ALF*Setup!$C$15,HALF*Setup!$C$17))+(Setup!$C$19*CO RNERS/2))/VLOOKUP(TYPE,ProductData,6,FALSE)),BBGROUT),IF(Spa cg<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 spacg8 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

Formula evaluation
 

#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(Spacg8, then you can eliminate
the lookup as a possible cause since it's common to both IFs. So, your
problem is somewhere in he

(LENGTH*HEIGHT*QTY*EA*1.125)

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

--
Biff
Microsoft Excel MVP


"Edward" wrote in message
...
Can someone review the following formula and tell me why I am getting the
#Value error?

=IF(Spacg8,(FULL/VLOOKUP(TYPE,ProductData,6,FALSE)/((Spacg/8)))+(BBGROUT+BEAM*EA*QTY)+(((IF(ISBLANK(LINTEL),H ALF*Setup!$C$15,HALF*Setup!$C$17))+(Setup!$C$19*CO RNERS/2))/VLOOKUP(TYPE,ProductData,6,FALSE)),BBGROUT),IF(Spa cg<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 spacg8 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

Formula evaluation
 

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
Spacg8
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.