Formula output not read as a number

[Panmanager]

I am using a formula in a couple of cells that excel doesn't pick up as a number.

In cell c15, I am adding up cells c12 through c14. In that cell, I get the error message "A value used in the formula is the wrong data type."

In c12 and c13, I am using a formula, which I assume, excel doesn't see the output as a number. How do I rewrite the formula so that I can continue? (Please see my screen shot below).

Thank you


-- removed inline image ---

 

[Panmanager]

I guess I can't use a screen shot.

This is the formula I am trying to use:
=IF(C3<1,"",IF(AND(C3>=1,C3<=20),"0","")&IF(AND(C3>=21,C3<=59),"1.5","")&IF(AND(C3>=64,C3<=104),"2.0","")&IF(AND(C3>=105,C3<=135),"2.5","")&IF(AND(C3>=136,C3<=165),"3.0","")&IF(AND(C3>=166,C3<=225),"3.5","")&IF(AND(C3>=226,C3<=285),"4.0","")&IF(AND(C3>=286,C3<=326),"4.5","")&IF(AND(C3>=327,C3<=366),"5.0","")&IF(AND(C3>=367,C3<=425),"5.5","")&IF(AND(C3>=426,C3<=483),"6.0","")&IF(AND(C3>=484,C3<=531),"6.5","")&IF(AND(C3>=532,C3<=579),"7.0","")&IF(AND(C3>=580,C3<=643),"7.5","")&IF(AND(C3>=644,C3<=707),"8.0","")&IF(AND(C3>=708,C3<=768),"8.5","")&IF(AND(C3>=769,C3<=828),"9.0","")&IF(AND(C3>=829,C3<=881),"9.5","")&IF(AND(C3>=882,C3<=935),"10.0",""))

 

[jtakw]

I guess I can't use a screen shot.

This is the formula I am trying to use:
=IF(C3<1,"",IF(AND(C3>=1,C3<=20),"0","")&IF(AND(C3>=21,C3<=59),"1.5","")&IF(AND(C3>=64,C3<=104),"2.0","")&IF(AND(C3>=105,C3<=135),"2.5","")&IF(AND(C3>=136,C3<=165),"3.0","")&IF(AND(C3>=166,C3<=225),"3.5","")&IF(AND(C3>=226,C3<=285),"4.0","")&IF(AND(C3>=286,C3<=326),"4.5","")&IF(AND(C3>=327,C3<=366),"5.0","")&IF(AND(C3>=367,C3<=425),"5.5","")&IF(AND(C3>=426,C3<=483),"6.0","")&IF(AND(C3>=484,C3<=531),"6.5","")&IF(AND(C3>=532,C3<=579),"7.0","")&IF(AND(C3>=580,C3<=643),"7.5","")&IF(AND(C3>=644,C3<=707),"8.0","")&IF(AND(C3>=708,C3<=768),"8.5","")&IF(AND(C3>=769,C3<=828),"9.0","")&IF(AND(C3>=829,C3<=881),"9.5","")&IF(AND(C3>=882,C3<=935),"10.0",""))

Hi,

See above in red, you have assigned all the results of your formula as TEXT, remove ALL the quote marks around all your number results in the formula.

 

[Aladin Akyurek]

I guess I can't use a screen shot.

This is the formula I am trying to use:
=IF(C3<1,"",IF(AND(C3>=1,C3<=20),"0","")&IF(AND(C3>=21,C3<=59),"1.5","")&IF(AND(C3>=64,C3<=104),"2.0","")&IF(AND(C3>=105,C3<=135),"2.5","")&IF(AND(C3>=136,C3<=165),"3.0","")&IF(AND(C3>=166,C3<=225),"3.5","")&IF(AND(C3>=226,C3<=285),"4.0","")&IF(AND(C3>=286,C3<=326),"4.5","")&IF(AND(C3>=327,C3<=366),"5.0","")&IF(AND(C3>=367,C3<=425),"5.5","")&IF(AND(C3>=426,C3<=483),"6.0","")&IF(AND(C3>=484,C3<=531),"6.5","")&IF(AND(C3>=532,C3<=579),"7.0","")&IF(AND(C3>=580,C3<=643),"7.5","")&IF(AND(C3>=644,C3<=707),"8.0","")&IF(AND(C3>=708,C3<=768),"8.5","")&IF(AND(C3>=769,C3<=828),"9.0","")&IF(AND(C3>=829,C3<=881),"9.5","")&IF(AND(C3>=882,C3<=935),"10.0",""))

Never double quote numeric items...

Instead of a lengthy IF, you can do a fast table look up say with LOOKUP or VLOOKUP, etc. Using VLOOKUP, you can have:

=VLOOKUP(C3,{0,"";1,0;21,1.5;60,"?";64,2;105,2.5;136,3;166,3.5;226,4;286,4.5;
    327,5;367,5.5;426,6;484,6.5;532,7;580,7.5;644,8;708,8.5;769,9;829,9.5;882,10},2,1)

 

[Panmanager]

Thank you Aladin. The formula works perfectly.

I do have another question. If c3 is empty, I get the error message "the value used in the formula is the wrong data type".

I can fix it by putting a 0 in c3 and a 0 within the quotes in the formula, but if I want a cell with no value to continue to stay empty, how would I change the formula? I would prefer to see an empty cell rather than a 0.

Is there something else that needs to be changed in the formula.

Thank you

Never double quote numeric items...

Instead of a lengthy IF, you can do a fast table look up say with LOOKUP or VLOOKUP, etc. Using VLOOKUP, you can have:

=VLOOKUP(C3,{0,"";1,0;21,1.5;60,2.0;64,2;105,2.5;136,3;166,3.5;226,4;286,4.5;
    327,5;367,5.5;426,6;484,6.5;532,7;580,7.5;644,8;708,8.5;769,9;829,9.5;882,10},2,1)

 

[Aladin Akyurek]

Thank you Aladin. The formula works perfectly.

I do have another question. If c3 is empty, I get the error message "the value used in the formula is the wrong data type".

I can fix it by putting a 0 in c3 and a 0 within the quotes in the formula, but if I want a cell with no value to continue to stay empty, how would I change the formula? I would prefer to see an empty cell rather than a 0.

Is there something else that needs to be changed in the formula.

Thank you

If C3 is empty, I don't get such a message. Rather VLOOKUP returns a value, say, in D3 of which ISTEXT is TRUE. That means we can't do in E3 for example something like:

=D3+5 >> #VALUE!

while the following will succeed:

=SUM(D3,5) >> 5

If you would like to return 0, replace the inlined value of "" (see the formula) to 0 (not "0") and custom format the formula cell as:

[=0]"";General

 

[Joe4]

I guess I can't use a screen shot.

Check out the "Posting Aids" section at the bottom of this post here: http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html