Variable decimal format: displayed value differs from the value used when referenced in another formula

[yellowcedar]

Currently I am trying to have variable decimal formats displayed across a range of cells. The amount of decimals will depend on how large the numbers are. The numbers range from 100,000.00 to .000000001. I can get this formula to work properly and it displays the decimals as I have anticipated, however when I now try to reference any of these cells elsewhere it always sees the cell as having a value of 1.

I have used the following formula in each cell within my range:

=IF(VLOOKUP(C9, Totals!$A$2:$P$6251, 2, FALSE) >= 100, TEXT(VLOOKUP(C9, Totals!$A$2:$P$6251, 2, FALSE), "###,##.00"),IF(VLOOKUP(C9, Totals!$A$2:$P$6251, 2, FALSE) >= 0.1, TEXT(VLOOKUP(C9, Totals!$A$2:$P$6251, 2, FALSE), "###,##.00000"),IF(VLOOKUP(C9, Totals!$A$2:$P$6251, 2, FALSE) >= 0.0001, TEXT(VLOOKUP(C9, Totals!$A$2:$P$6251, 2, FALSE), "###,##.00000000"),IF(VLOOKUP(C9, Totals!$A$2:$P$6251, 2, FALSE) >= 0.000000000000000001, TEXT(VLOOKUP(C9, Totals!$A$2:$P$6251, 2, FALSE), "###,##.0000000000"),""))))

For example if Cell A9 has a value of 100 and cell Q9 has the above formula in it, and I try to do an set B9 to =PRODUCT(A9,Q9) it will return a value of 100 for B9. The value displayed in cell Q9 is 10,000.00 and so the result should be 1,000,000.00 in B9. It seems like it is displaying one value but calculating other formulas using another hidden value.

What am I doing wrong here? and is there a better way for build this formula that would prevent this?

 

[yellowcedar]

to clarify I am trying to accomplish the following:

use VLOOKUP to search for a name on another sheet and export that specific column's value into the current sheet's cell range of Q9:Q20

VLOOKUP(C9, Totals!$A$2:$P$6251, 2, FALSE)

then

In this range of cells (Q9:Q20)
set any number format to 2 decimals if that number is over 100
set any number format to 5 decimals if that number is over 0.1
set any number format to 8 decimals if that number is over 0.0001
set any number format to 10 decimals if that number is over 0.000000000000000001
(I have attempted the nested IF formula in my post above)

Multiply the above values in cells Q9:Q20 by cells A9:A20
and display this in cells B9:B20

Problem: Cells B9:B20 display the wrong value. Since it is multiplying the data in A always by 1 instead of actually multiplying by the calculated value that my formula is outputting

 

[Peter_SSs]

Welcome to the MrExcel board!

What am I doing wrong here?

The issue is that the TEXT function (unsurprisingly) returns a text value, not a number. So the result of your formula in Q9 is a text value. The PRODUCT function ignores text values so =PRODUCT(A9, Q9) ignores Q9 (text value) and that is why it is just returning the A9 value.
Try either of these formulas in B9 instead. They will "coerce" the text value that looks like a number back into an actual number so the multiplication can take place.

=A9*Q9

=PRODUCT(A9,--Q9)

 

[yellowcedar]

Welcome to the MrExcel board!


The issue is that the TEXT function (unsurprisingly) returns a text value, not a number. So the result of your formula in Q9 is a text value. The PRODUCT function ignores text values so =PRODUCT(A9, Q9) ignores Q9 (text value) and that is why it is just returning the A9 value.
Try either of these formulas in B9 instead. They will "coerce" the text value that looks like a number back into an actual number so the multiplication can take place.

=A9*Q9

=PRODUCT(A9,--Q9)

Thanks very much. Yeah all I had to do was change B9 to =PRODUCT(A9*Q9)

 

[Peter_SSs]

Thanks very much. Yeah all I had to do was change B9 to =PRODUCT(A9*Q9)

PRODUCT there is pointless
A9*Q9 already is a product so your formula is like saying =PRODUCT(56). It is just 56
As I mentioned above, just use
=A9*Q9