How to insert a number as a number and not text

[dpaton05]

I have the following formula in a total price cell for a spreadsheet that generates quotes. The cell has to lookup info if the drop down menu under "what service" cell has something other than respite care. If it has respite care, the price needs to be $150, regardless of the inputs in the other cells. With the following formula, $150 gets put in the price cell, not as a number but as text. By that I mean that all the other line in this pricing sheet, are right aligned, as with all numbers, but if I have respite care selected, the $150 is left aligned, as with text.

=IF([@[What service]]="Respite Care","$150.00",IF(OR(ISBLANK(A14),ISBLANK(C14),ISBLANK(D14),ISBLANK(E14)),0,((INDEX(Max_pay_1hr,MATCH([@[What service]],W4:W7,0),MATCH([@Column1],W4:Z4,0))+INDEX(extra_pickup_location,MATCH([@[What service]],$P$4:$P$8,0),MATCH([@Column1],P4:S4,0))*[@[Extra pickups, up to 3 CYP]]+INDEX(extra_hours_over_1,MATCH([@[What service]],$P$11:$P$15,0),MATCH([@Column1],$P$11:$S$11,0)))*[@[No. workers required]])))


How do I get it so the $150 gets entered as a number and has right alignment? Is the IF statement at the front the right formula as I couldn't think of what it should be?

 

[Michael M]

Have you tried formatting the cell with the $150 as number, or better still currency, not text ?

 

[dpaton05]

Yep, tried that. It is formatted as currency.

 

[Peter_SSs]

I have the following formula in a total price cell for a spreadsheet that generates quotes. The cell has to lookup info if the drop down menu under "what service" cell has something other than respite care. If it has respite care, the price needs to be $150, regardless of the inputs in the other cells. With the following formula, $150 gets put in the price cell, not as a number but as text. By that I mean that all the other line in this pricing sheet, are right aligned, as with all numbers, but if I have respite care selected, the $150 is left aligned, as with text.

=IF([@[What service]]="Respite Care","$150.00",IF(OR(ISBLANK(A14),ISBLANK(C14),ISBLANK(D14),ISBLANK(E14)),0,((INDEX(Max_pay_1hr,MATCH([@[What service]],W4:W7,0),MATCH([@Column1],W4:Z4,0))+INDEX(extra_pickup_location,MATCH([@[What service]],$P$4:$P$8,0),MATCH([@Column1],P4:S4,0))*[@[Extra pickups, up to 3 CYP]]+INDEX(extra_hours_over_1,MATCH([@[What service]],$P$11:$P$15,0),MATCH([@Column1],$P$11:$S$11,0)))*[@[No. workers required]])))


How do I get it so the $150 gets entered as a number and has right alignment? Is the IF statement at the front the right formula as I couldn't think of what it should be?

That is because you are telling it to put text $150.
Try removing the text quotes & the $ sign.

 

[dpaton05]

Thanks, that worked

 

[Peter_SSs]

Thanks, that worked

You're welcome.