I need help with 'value_if_true'

[Thanks!]

I am by no means skilled in Excel but I need help with a formula. I am using
the spreadsheet to calculate product prices based on my cost. My sister
helped me originally with the first two formulas and they still work
perfectly but I can't seem to make new formulas using the same concept. The
variable that I don't understand is $AA$x (x being 1,3,6 or 8).

Here are the two formulas that do work:

=IF((F2/64)*16<5,$AA$1,(F2/64*16))
=IF((F2/16)*8<8,$AA$3,(F2/16*8))

Now here are the three formulas I want to make work:
=IF(((F2/64)*16)*3<18,$AA$3,((F2/64)*16)*3)
=IF((F2/2)*3<26,$AA$3,(F2/2)*3)
=IF((F2*2.5)<38,$AA$3,(F2*2.5))


The 3 after $AA$ seems to be the key but I don't know what it means nor can
I find $AA$ in Excel Help anywhere.

Any help please?

 

[akphidelt]

$AA$3 is a cell reference. So that is saying if the expression is TRUE then
take whatever is in cell AA3.

The $ signs are absolute references so if you were to copy that formula it
would retain the $AA$3. If you took off the $ signs and made it AA3 then the
cell reference would move as you copied and pasted.

 

[T. Valko]

=IF(((F2/64)*16)*3<18,$AA$3,((F2/64)*16)*3)

The 3 after $AA$ seems to be the key but I don't know what it means

$AA$3 is a cell address that refers to column AA row 3.

The formulas will return whatever's in that cell address if
((F2/64)*16)*3<18 = TRUE.

The $ signs make both the column and the row references absolute. This means
that if the formula was copied to other cells the reference to AA3 will not
change.

 

[JE McGimpsey]

The 3 after $AA$ is just the row number for the value_if_true cell
reference in column AA (the $ indicate that the reference is absolute -
look up absolute and relative references in Help).

Also, perhaps simplifying the formulae would help:

Working:

=IF(F2/4 < 5, $AA$1, F2/4)
=IF(F2/2 < 8, $AA$3, F2/2)

Not working:

=IF(F2*0.75 < 18, $AA$3, F2*0.75)
=IF(F2*1.5 < 26, $AA$3, F2*1.5)
=IF(F2*2.5 < 38, $AA$3, F2*2.5)

or, equivalently:

=IF(F2<20, $AA$1, F2/4)
=IF(F2<16, $AA$3, F2/2)

=IF(F2<24, $AA$3, F2*0.75)
=IF(F2<52/3, $AA$3, F2*1.5)
=IF(F2<15.2, $AA$3, F2*2.5)

 

[Gord Dibben]

$AA$3 is a cell reference in absolute mode.

AA3 would be relative mode.

What do you have in that cell?

What doesn't work that does work in the two that work?

I tested all and look OK to me.


Gord Dibben MS Excel MVP