Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

problem with multiple if function and whole numbers

Hi! My first post here!

I'm creating a conversion tool which takes a code number (913, 858 or 471 for example) entered in one cell and converts that number to another code in an adjacent cell.


Input Cell A1: 913

Output Cell B1: 3.1

I've got about 50 input codes to deal with, so I opted to use a multiple IF function which I researched here and came up with something like this:

Multiple IF in B Column: =IF(A1=615,1.85) & IF(A1=270,1.37) & IF(A1=405,0.94) & IF(A1=550,0.75)

However, since I needed to use SUM to tally the total, I used the VALUE function to convert the B Column output to numbers by doing this:

Multiple IF in B Column with added VALUE: =VALUE( IF(A1=615,1.85) & IF(A1=270,1.37) & IF(A1=405,0.94) & IF(A1=550,0.75) )

The problem is that if the converted number is a whole number ( IF(A1=333,6.00) ), that output is very odd. Depending upon where in the code that whole number is, the output could be 60, 6000, or in one instance, it output 0000600.

My question is this: How do I overcome this issue with whole numbers within this function?

Thank you all!

PS. I'm only capable of the most basic coding chores, so hopefully your response will utilize my present code, but if there is an equally simple way to do this, I'm open to another method as well.