Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
$ SIGN WITH FORMULA
I HAVE GOTTEN THE FORMULA TO WORK BUT WOULD LIKE TO ADD THE $ SIGN IN FRONT
OF THE ANSWER SINCE IT REPRESENTS A DOLLAR AMOUNT =CONCATENATE(IF(E7="A",510.09,""),IF(E7="B",604.96 ,"") |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
$ SIGN WITH FORMULA
Why don't you use simply =IF(E7="A",510.09,604.96) and format the result cell
as Currency? Regards, Stefi €˛LostNFound€¯ ezt Ć*rta: I HAVE GOTTEN THE FORMULA TO WORK BUT WOULD LIKE TO ADD THE $ SIGN IN FRONT OF THE ANSWER SINCE IT REPRESENTS A DOLLAR AMOUNT =CONCATENATE(IF(E7="A",510.09,""),IF(E7="B",604.96 ,"") |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
$ SIGN WITH FORMULA
The formula string is a little longer than I put and the letters are not
sequential =CONCATENATE(IF(D7="A",510.09,""),IF(D7="B",604.96 ,""),IF(D7="C",672.093,""),IF(D7="D",740.74,""),IF (D7="E",808.69,""),IF(D7="F",869.10,""),IF(D7="I", 1069.69,"")) I tried to format this cell as currency and two deimal places but do not get $$ and also the section with 869.10 returns only 869.1 "Stefi" wrote: Why don't you use simply =IF(E7="A",510.09,604.96) and format the result cell as Currency? Regards, Stefi €˛LostNFound€¯ ezt Ć*rta: I HAVE GOTTEN THE FORMULA TO WORK BUT WOULD LIKE TO ADD THE $ SIGN IN FRONT OF THE ANSWER SINCE IT REPRESENTS A DOLLAR AMOUNT =CONCATENATE(IF(E7="A",510.09,""),IF(E7="B",604.96 ,"") |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
$ SIGN WITH FORMULA
This may be a nice solution if you can use a continuous series of letters
from A to G that is if you can change I to G in the last case: =INDEX({510.09;604.96;672.093;740.74;808.69;869.1; 1069.69},COLUMN(INDIRECT(D7&1))) Maybe you have to change semicolons to commas between array elements, it depend on your XL language version! Using CONCATENATE converts numbers to strings, that's why formatting to 2 decimals has no effect! Regards, Stefi €˛LostNFound€¯ ezt Ć*rta: The formula string is a little longer than I put and the letters are not sequential =CONCATENATE(IF(D7="A",510.09,""),IF(D7="B",604.96 ,""),IF(D7="C",672.093,""),IF(D7="D",740.74,""),IF (D7="E",808.69,""),IF(D7="F",869.10,""),IF(D7="I", 1069.69,"")) I tried to format this cell as currency and two deimal places but do not get $$ and also the section with 869.10 returns only 869.1 "Stefi" wrote: Why don't you use simply =IF(E7="A",510.09,604.96) and format the result cell as Currency? Regards, Stefi €˛LostNFound€¯ ezt Ć*rta: I HAVE GOTTEN THE FORMULA TO WORK BUT WOULD LIKE TO ADD THE $ SIGN IN FRONT OF THE ANSWER SINCE IT REPRESENTS A DOLLAR AMOUNT =CONCATENATE(IF(E7="A",510.09,""),IF(E7="B",604.96 ,"") |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
$ SIGN WITH FORMULA
If you don't mind the number returned as text,
then: =TEXT(formula,"$#,##0.00") would achieve the result So you could try: =TEXT(CONCATENATE(IF(D7="A",510.09,""),IF(D7="B",6 04.96,""),IF(D7="C",672.093,""),IF(D7="D",740.74," "),IF(D7="E",808.69,""),IF(D7="F",869.1,""),IF(D7= "I",1069.69,"")),"$#,##0.00") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "LostNFound" wrote: The formula string is a little longer than I put and the letters are not sequential =CONCATENATE(IF(D7="A",510.09,""),IF(D7="B",604.96 ,""),IF(D7="C",672.093,""),IF(D7="D",740.74,""),IF (D7="E",808.69,""),IF(D7="F",869.10,""),IF(D7="I", 1069.69,"")) I tried to format this cell as currency and two deimal places but do not get $$ and also the section with 869.10 returns only 869.1 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
$ SIGN WITH FORMULA
You could nest your if statements and then format the result to whatever you
want. =(IF(D7="A",510.09,IF(D7="B",604.96,IF(D7="C",672. 093,IF(D7="D",740.74,IF(D7="E",808.69,IF(D7="F",86 9.1,IF(D7="I",1069.69,"")))))))) Gary "Stefi" wrote: This may be a nice solution if you can use a continuous series of letters from A to G that is if you can change I to G in the last case: =INDEX({510.09;604.96;672.093;740.74;808.69;869.1; 1069.69},COLUMN(INDIRECT(D7&1))) Maybe you have to change semicolons to commas between array elements, it depend on your XL language version! Using CONCATENATE converts numbers to strings, that's why formatting to 2 decimals has no effect! Regards, Stefi €˛LostNFound€¯ ezt Ć*rta: The formula string is a little longer than I put and the letters are not sequential =CONCATENATE(IF(D7="A",510.09,""),IF(D7="B",604.96 ,""),IF(D7="C",672.093,""),IF(D7="D",740.74,""),IF (D7="E",808.69,""),IF(D7="F",869.10,""),IF(D7="I", 1069.69,"")) I tried to format this cell as currency and two deimal places but do not get $$ and also the section with 869.10 returns only 869.1 "Stefi" wrote: Why don't you use simply =IF(E7="A",510.09,604.96) and format the result cell as Currency? Regards, Stefi €˛LostNFound€¯ ezt Ć*rta: I HAVE GOTTEN THE FORMULA TO WORK BUT WOULD LIKE TO ADD THE $ SIGN IN FRONT OF THE ANSWER SINCE IT REPRESENTS A DOLLAR AMOUNT =CONCATENATE(IF(E7="A",510.09,""),IF(E7="B",604.96 ,"") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
how do i use & sign without it thinking its a formula | Excel Worksheet Functions | |||
Reusing formula | Excel Discussion (Misc queries) | |||
an extra "+" sign in formula | Excel Discussion (Misc queries) | |||
How do I get an automatic equal sign to begin the formula bar? | New Users to Excel |