Problem with multiple if function and whole numbers

[ggatlanta]

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.

Example:

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.

[librebel]

Hello @ggatlanta,

Try if setting the “Number Format” for column B helps:

1. Select column B by clicking on the column header,
2. Right-click in column B and select “Format Cells...”,
3. in the dialog that pops up, select the tab called “Numbers”,
4. Type “0.00” ( without the quotes ) in the textbox called “Format code”
5. Press OK.

[Lupp]

As long as there isn’t a clear specification of the assignment of secondary codes to the primary ones I cannot offer “positive” advice.

Negative: All your conditional expressions are of the type IF(Condition;Number) and thus incomplete: The else part is missing. The IF function must nonetheless return something also if the condition comes out FALSE. In the absence of the else (otherwise) part this will be the FALSE itself treated as 0 (zero) by the automatic conversion into text.

Generally consider a basical redesign of your attempts.

(The usage of fractional numbers for the secondary codes is a mystery to me. I cannot reasonably guess what you actually want to achieve. If you want to assign explicitly one specific code to any allowed input: Dispose of the IF-chain and use a lookup-table. )

Edit: Decided to attach this example I made yesterday.

[ggatlanta]

Thanks, all. I ended up redoing the spreadsheet in Google Sheet, which did not have this issue.

Thanks anyway.