Formulas that reference cells that reference another cell

[Guest]

Is it possible to build a formula that reads the value in a cell (that
references another cell) versus reading the formula in a cell?

For example, if a greater than value: IF A4>A5, but the value in A4 is a
reference from another table, is there a way to do this, so the formula reads
the value, not the formula within the cell?

Thanks!

 

[Guest]

All formulas read values. Some values are hard-coded and some are the result
of other formulas' calculations.

So what are you trying to do here? If A4=C5 and you want to test if A5 is
less than A4, you could just enter in, say B2: =IFA4>A5,"bigger","smaller"
Excel will look at A4, see its reference to C5 and calculate based on the
value in C5. And if C5 contains a formula, it will iterate the process until
it finds a final value.

Dave

 

[Guest]

What if it A4=C5 (C5 is in another workbook in my example), and i wanted the
IF statement to read as follows =IFA4>A5, A4, A5.

Will it still work the same, the if A4 was greater than A5, the value
returned is A4, referencing the value in C5?

I think my formula is correct, because it returns the correct values if I
pick two other cells to compare, just not the cells that are pulling data
from the additional workbook.

THanks.

 

[Guest]

Yes, your formula will work.

 

[Guest]

It is not working. The A4>A5 is giving me a false statement, when it should
be true. For example, even if the formula in A4 results in the number 10,
and A5=8, it is telling me that the logic statement is false, and returning a
value of 8. That is why I was questioning the ability of excel to
distinguish between the fomula within a cell and the value of the formula in
a logic statement. The formula works on any two cells that have numeric
values, just not when a cell that is included is referencing another cell.

Any other thoughts?

Thanks!
Andrea

 

[David Biddulph]

Are you sure that A4 is returning the number 10, not a text value? If in
doubt, try a helper cell with the formula =A4, formatted as a number.

 

[Guest]

this is what my formula reads: IF(Cover!G3<=$H$12,$H$12,Cover!G3)
What I want is that if the CoverG3 is less or equal to the value in H12,
which is a value that is being pulled by a formula from another workbookm
then it returns the value in h12. If CoverG3 is greater than H12, than it
should return CoverG3. Is this correct? Even if I use a helper cell
(H13=h12, and then substiture H13 in the above formula and it is formatted as
a number), it still does not work.

Maybe I am missing something?

 

[David Biddulph]

Well now you're talking about a different arrangement of the formula
compared with what you described earlier, but the question still remains as
to what is going in to the formula.
Set up a helper cell with the formula =Cover!G3, format it as a number;
what is the value?
Set up a helper cell with the formula =$H$12, format it as a number; what is
the value?
What value are you getting out of your =IF(Cover!G3<=$H$12,$H$12,Cover!G3)
formula?
You are sure that the calculation mode [in Tools/ Options/ Calculation] is
set to Automatic?

David Biddulph