Forum Discussion
Disappearing Value
I have inherited an Excel estimating work book running on Windows 10, (Excel 2016) which I have to update to operate in different currencies, One of the sheets contains a price list which we wish to automatically update depending on the currency and Fx rate . These values are entered into a cover sheet, the resulting values are in a narrow column (0.42 pt) and aligned right so that they spill onto the empty column on the left
To achieve this I am using the following formula:
IF(COVER!$C$50="£",WORKSHEET!AL440,ROUNDUP(WORKSHEET!AL440*COVER!$C$52,0))
Cover C50 contains the currency symbol
Cover C52 contains the Fx multiplier
Worksheet AL440 contains the base cost
Everything works fine if we are using the £ symbol, if we use $ or € the result is not displayed as the cell does not behave as if it is aligned right, expanding the column displays the correct result
Can anyone help with his?
- Thanks for that AL was in fact formatted as text so you were correct and the rest of the formula the result of multiplications. I had just solved it another way by appending & "" to the end of the formula. Many thanks for the help
2 Replies
I suspect that AL440 contains a text value that looks like a number.
Text values can be displayed spilling into the next column(s), but numbers (and dates) cannot.
When C50 contains £, the formula returns the value of AL440 itself. If this is text, as I suspect, this will spill into the next column. But if C50 contains another currency symbol, the formula returns the result of a multiplication, and that is a number, so it won't spill.
- Thanks for that AL was in fact formatted as text so you were correct and the rest of the formula the result of multiplications. I had just solved it another way by appending & "" to the end of the formula. Many thanks for the help
