Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I get a function formula cell to format as a number?
I put this formula "=IF(OR($C$9="Check",$C$9="Decrease
Adjustment"),$G$8,"0")" and the cell formats as a number but when I put this formula "=(IF($C$8="Enter Transaction",IF(OR($C$9="Withdrawal",$C$9="Increas e Adjustment"),$G$8,""),""))&(IF($C$8="Enter Receipt",IF(OR($C$9="Check",$C$9="Decrease Adjustment"),$G$8,""),""))" it doesn't. I have gone into the fomatting of the cell and it shows that it should be viewing appropriately but it is not doing it. Can someone tell me what I am not doing in order to get the second function formula to show as as number? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I get a function formula cell to format as a number?
The reason is becaue the formula returns a TEXT value even if it looks like
a number. You're concatenating 2 results together and this forces the result to be TEXT. for example: =10&".0" Result = 10.0 It *looks* like a number but it isn't, it's a TEXT string. So, if the result of the first half of your formula is G8 (which I assume is a number) and the result of the second half of the formula is blank ("") then you get: G8&"" Let's assume G8 = 100. G8&"" = 100 It *looks* like a number but it isn't, it's a TEXT string. -- Biff Microsoft Excel MVP "Kristin" wrote in message ... I put this formula "=IF(OR($C$9="Check",$C$9="Decrease Adjustment"),$G$8,"0")" and the cell formats as a number but when I put this formula "=(IF($C$8="Enter Transaction",IF(OR($C$9="Withdrawal",$C$9="Increas e Adjustment"),$G$8,""),""))&(IF($C$8="Enter Receipt",IF(OR($C$9="Check",$C$9="Decrease Adjustment"),$G$8,""),""))" it doesn't. I have gone into the fomatting of the cell and it shows that it should be viewing appropriately but it is not doing it. Can someone tell me what I am not doing in order to get the second function formula to show as as number? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I get a function formula cell to format as a number?
How would I get the cell to return with a numeric value?
"T. Valko" wrote: The reason is becaue the formula returns a TEXT value even if it looks like a number. You're concatenating 2 results together and this forces the result to be TEXT. for example: =10&".0" Result = 10.0 It *looks* like a number but it isn't, it's a TEXT string. So, if the result of the first half of your formula is G8 (which I assume is a number) and the result of the second half of the formula is blank ("") then you get: G8&"" Let's assume G8 = 100. G8&"" = 100 It *looks* like a number but it isn't, it's a TEXT string. -- Biff Microsoft Excel MVP "Kristin" wrote in message ... I put this formula "=IF(OR($C$9="Check",$C$9="Decrease Adjustment"),$G$8,"0")" and the cell formats as a number but when I put this formula "=(IF($C$8="Enter Transaction",IF(OR($C$9="Withdrawal",$C$9="Increas e Adjustment"),$G$8,""),""))&(IF($C$8="Enter Receipt",IF(OR($C$9="Check",$C$9="Decrease Adjustment"),$G$8,""),""))" it doesn't. I have gone into the fomatting of the cell and it shows that it should be viewing appropriately but it is not doing it. Can someone tell me what I am not doing in order to get the second function formula to show as as number? . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I get a function formula cell to format as a number?
Try it like this...
All on one line =IF(OR($C$8={"Enter Transaction","Enter Receipt"}), IF(OR($C$9={"Withdrawl","Increase Adjustment", "Check","Decrease Adjustment"}),$G$8,""),"") -- Biff Microsoft Excel MVP "Kristin" wrote in message ... How would I get the cell to return with a numeric value? "T. Valko" wrote: The reason is becaue the formula returns a TEXT value even if it looks like a number. You're concatenating 2 results together and this forces the result to be TEXT. for example: =10&".0" Result = 10.0 It *looks* like a number but it isn't, it's a TEXT string. So, if the result of the first half of your formula is G8 (which I assume is a number) and the result of the second half of the formula is blank ("") then you get: G8&"" Let's assume G8 = 100. G8&"" = 100 It *looks* like a number but it isn't, it's a TEXT string. -- Biff Microsoft Excel MVP "Kristin" wrote in message ... I put this formula "=IF(OR($C$9="Check",$C$9="Decrease Adjustment"),$G$8,"0")" and the cell formats as a number but when I put this formula "=(IF($C$8="Enter Transaction",IF(OR($C$9="Withdrawal",$C$9="Increas e Adjustment"),$G$8,""),""))&(IF($C$8="Enter Receipt",IF(OR($C$9="Check",$C$9="Decrease Adjustment"),$G$8,""),""))" it doesn't. I have gone into the fomatting of the cell and it shows that it should be viewing appropriately but it is not doing it. Can someone tell me what I am not doing in order to get the second function formula to show as as number? . |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I get a function formula cell to format as a number?
Thank you VERY MUCH. That works exactly like what I was looking for.
"T. Valko" wrote: Try it like this... All on one line =IF(OR($C$8={"Enter Transaction","Enter Receipt"}), IF(OR($C$9={"Withdrawl","Increase Adjustment", "Check","Decrease Adjustment"}),$G$8,""),"") -- Biff Microsoft Excel MVP "Kristin" wrote in message ... How would I get the cell to return with a numeric value? "T. Valko" wrote: The reason is becaue the formula returns a TEXT value even if it looks like a number. You're concatenating 2 results together and this forces the result to be TEXT. for example: =10&".0" Result = 10.0 It *looks* like a number but it isn't, it's a TEXT string. So, if the result of the first half of your formula is G8 (which I assume is a number) and the result of the second half of the formula is blank ("") then you get: G8&"" Let's assume G8 = 100. G8&"" = 100 It *looks* like a number but it isn't, it's a TEXT string. -- Biff Microsoft Excel MVP "Kristin" wrote in message ... I put this formula "=IF(OR($C$9="Check",$C$9="Decrease Adjustment"),$G$8,"0")" and the cell formats as a number but when I put this formula "=(IF($C$8="Enter Transaction",IF(OR($C$9="Withdrawal",$C$9="Increas e Adjustment"),$G$8,""),""))&(IF($C$8="Enter Receipt",IF(OR($C$9="Check",$C$9="Decrease Adjustment"),$G$8,""),""))" it doesn't. I have gone into the fomatting of the cell and it shows that it should be viewing appropriately but it is not doing it. Can someone tell me what I am not doing in order to get the second function formula to show as as number? . . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I get a function formula cell to format as a number?
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Kristin" wrote in message ... Thank you VERY MUCH. That works exactly like what I was looking for. "T. Valko" wrote: Try it like this... All on one line =IF(OR($C$8={"Enter Transaction","Enter Receipt"}), IF(OR($C$9={"Withdrawl","Increase Adjustment", "Check","Decrease Adjustment"}),$G$8,""),"") -- Biff Microsoft Excel MVP "Kristin" wrote in message ... How would I get the cell to return with a numeric value? "T. Valko" wrote: The reason is becaue the formula returns a TEXT value even if it looks like a number. You're concatenating 2 results together and this forces the result to be TEXT. for example: =10&".0" Result = 10.0 It *looks* like a number but it isn't, it's a TEXT string. So, if the result of the first half of your formula is G8 (which I assume is a number) and the result of the second half of the formula is blank ("") then you get: G8&"" Let's assume G8 = 100. G8&"" = 100 It *looks* like a number but it isn't, it's a TEXT string. -- Biff Microsoft Excel MVP "Kristin" wrote in message ... I put this formula "=IF(OR($C$9="Check",$C$9="Decrease Adjustment"),$G$8,"0")" and the cell formats as a number but when I put this formula "=(IF($C$8="Enter Transaction",IF(OR($C$9="Withdrawal",$C$9="Increas e Adjustment"),$G$8,""),""))&(IF($C$8="Enter Receipt",IF(OR($C$9="Check",$C$9="Decrease Adjustment"),$G$8,""),""))" it doesn't. I have gone into the fomatting of the cell and it shows that it should be viewing appropriately but it is not doing it. Can someone tell me what I am not doing in order to get the second function formula to show as as number? . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function for euro number format? | Excel Worksheet Functions | |||
Format Cell formula vs number | Excel Discussion (Misc queries) | |||
Number format using TEXT( ) function | Excel Worksheet Functions | |||
function to count the number of cell references in a formula | Excel Worksheet Functions | |||
Setting Cell Number Format With A Worksheet Function | Excel Worksheet Functions |