Treat value in cell as number?

[tQk.Sushi]

Basically I have a column that uses a function to generate numbers. But when I try to use the =SUM function on that column, it doesn't sum up anything because they consider my function as text. Is there a way to make the values in cell be number so that the =SUM function can work?

Thank you.

 

[VoG]

What is your formula that generates the numbers?

 

[tQk.Sushi]

the formula is rather complicated but here it is:

=IF(AND($D3<$K$3,C3="Quadruple (4) Occupancy Room"),"185",IF(AND($D3<$K$3,C3="Triple (3) Occupancy Room"),"215",IF(AND($D3<$K$3,$C3="Double (2) Occupancy Room"),"265",IF(AND($D3<$K$3,$C3="Single (1) Occupancy Room"),"465",IF(AND($D3<=$K$3,C3="I will not be staying at the hotel"),"55",IF(AND($D3<$L$3,$D3>=$K$3,$C3="Quadruple (4) Occupancy Room"),"200",IF(AND($D3<$L$3,$D3>=$K$3,$C3="Triple (3) Occupancy Room"),"230",IF(AND($D3<$L$3,$D3>=$K$3,$C3="Double (2) Occupancy Room"),"280",IF(AND($D3<$L$3,$D3>=$K$3,$C3="Single (1) Occupancy Room"),"480",IF(AND($D3>=$K$3,$C3,$D3<$L$3="I will not be staying at the hotel"),"70",IF(AND($D3>=$L$3,C3="Quadruple (4) Occupancy Room"),"215",IF(AND($D3>=$L$3,C3="Triple (3) Occupancy Room"),"245",IF(AND($D3>=$L$3,C3="Double (2) Occupancy Room"),"295",IF(AND($D3>=$L$3,C3="Single (1) Occupancy Room"),"495",IF(AND($D3>=$L$3,C3="I will not be staying at the hotel"),"85",)))))))))))))))


it will either generate 185, 215, 265, etc. but I think the number that gets generated is considered "text" and because of that the =SUM function won't work.

 

[VoG]

Just remove the "" from the numbers like this

=IF(AND($D3<$K$3,C3="Quadruple (4) Occupancy Room"),185,IF(AND($D3<$K$3,C3="Triple (3) Occupancy Room"),215

 

[tQk.Sushi]

oh great, it works! Thanks alot!!

 

[tQk.Sushi]

actually i have one more question

there seems to be an error somewhere in my code. basically my code says if u register between 9/24/11 and 10/31/11 and you've requested to not stay at the hotel, the formula should generate 70. But mine just generates a 0.

I am referring to this part of my function:
IF(AND($D12>=$K$3,$C12,$D12<$L$3="I will not be staying at the hotel"),70

 

[VoG]

There seems to be something missing or should it be


IF(AND($D12>=$K$3,$D12<$L$3,$C12="I will not be staying at the hotel"),70

 

[tQk.Sushi]

thanks again, that was the problem.

This is one fantastic forum!