Error in formula

[cdsaroma]

Hi,

Why does the answer to the formula in cell AX24 return that AW24 is smaller (gives me an *) than AU24?

Cells
 AU23  | AW23   | AX23
$29.97 | $25.00 | =IF(AND(AW23>0,AU23>0,AW23<=AU23),("*"),("")) ---- this one returns an * and is correct

 AU24  | AW24    | AX24
   0     | $175.00 |
=IF(AND(AW24>0,AU24>0,AW24<=AU24),("*"),("")) ---- this one returns an * and is incorrect

AU23 & AU24 are a pastelink e.g ='Monthly Sold'!AA24
if there is no value in 'Monthly Sold'AA24 it returns a 0 (it is a sum field and has a function like:
=IF(ISERROR(AVERAGE(M24,O24,P24,Q24,R24,S24,T24,U24,V24,W24,X24,Y24,Z24)),"0", AVERAGE(M24,O24,P24,Q24,R24,S24,T24,U24,V24,W24,X24,Y24,Z24))

All the cells are formatted as currency

Things I've tried:
changing 'Monthly Sold'AA24 "0" to "0.00" but didn't work.
changing AU24>0 to AU24>"0" no go

thanks

 

[vaskov17]

the "0" part of the formula below shouldn't have the quotes

=IF(ISERROR(AVERAGE(M24,O24,P24,Q24,R24,S24,T24,U24,V24,W24,X24,Y24,Z24)),"0", AVERAGE(M24,O24,P24,Q24,R24,S24,T24,U24,V24,W24,X24,Y24,Z24))

 

[T. Valko]

Hi,

Why does the answer to the formula in cell AX24 return that AW24 is smaller (gives me an *) than AU24?

Cells
AU23 | AW23 | AX23
$29.97 | $25.00 | =IF(AND(AW23>0,AU23>0,AW23<=AU23),("*"),("")) ---- this one returns an * and is correct

AU24 | AW24 | AX24
0 | $175.00 |
=IF(AND(AW24>0,AU24>0,AW24<=AU24),("*"),("")) ---- this one returns an * and is incorrect

AU23 & AU24 are a pastelink e.g ='Monthly Sold'!AA24
if there is no value in 'Monthly Sold'AA24 it returns a 0 (it is a sum field and has a function like:
=IF(ISERROR(AVERAGE(M24,O24,P24,Q24,R24,S24,T24,U24,V24,W24,X24,Y24,Z24)),"0", AVERAGE(M24,O24,P24,Q24,R24,S24,T24,U24,V24,W24,X24,Y24,Z24))

All the cells are formatted as currency

Things I've tried:
changing 'Monthly Sold'AA24 "0" to "0.00" but didn't work.
changing AU24>0 to AU24>"0" no go

thanks

In your average formula remove the quotes from around the 0.

Quoting numbers makes them TEXT and TEXT is greater than any number.

 

[rdw72777]

While in your formula, highlight the text AU24, and hit F9. This will show you the value of AU24. (hit escape (if you don't it hard codes the value instead of the cell reference to AU24. You're right that based on what you typed its evaluating something wrong, I'm guessing its AU24 or AW24.

there could also be circular reference issues, when those happen formulas sometimes don't calc....

 

[cdsaroma]

Thanks everyone, it was the "0" that needed changing along with other formulas that included the same "0" I had in the sheet.

thanks again

 

[T. Valko]

Thanks everyone, it was the "0" that needed changing along with other formulas that included the same "0" I had in the sheet.

thanks again

You're welcome. We appreciate the feedback!