Excel formula for IF function

**HennieR** · 07-19-2018, 04:08 PM

I have been trying to create an IF formula to solve the following problem bu without success:

I want to display the different monetary values of a range of medical tariff codes, that a user would enter.
So, tariff code 84200 has a value of R200 (ZAR)
Tariff code 84201 has a value of R225.
Tarrif code 84202, a value of R250 and so on.
The money value increment is R25 for the codes starting at R200 for 84200 through to 84211.

What is the structure of the formula to display the Rand value when a code is entered?

Thanks
Hennie

**Sam Capricci** · 07-19-2018, 04:17 PM

Do the tariff codes ever jump more than one?

EDIT: since BSB came up with one, here is one I was playing with...
it assumes that A1 has 84200 and B1 has R200
and A2 is 84201 so this is in B2 dragged down...

="R"&IF(A2-A1=1,MID(B1,2,99)*1+25,B1)

**BadlySpelledBuoy** · 07-19-2018, 04:17 PM

If your code was in A1 and stored as a number, you could use the below:

Formula:

Please Login or Register  to view this content.

I'm sure there are many ways of accomplishing the same result.

BSB

**mehmetcik** · 07-19-2018, 04:20 PM

If your codes are 84200 to 84211 in increments of 25 then you do not need an if function

Formula:

Please Login or Register  to view this content.

Where A1 is your code.

If in the future you decide to use variable increments then

Formula:

Please Login or Register  to view this content.

Which uses the following table

84200 200
84201 227
84202 255
84203 284
84204 314
84205 345
84206 377
84207 410
84208 444
84209 479
84210 515
84211 552

**HennieR** · 07-19-2018, 04:25 PM

No they dont. they start at 200 and increase by one to 211

**Sam Capricci** · 07-19-2018, 04:28 PM

then almost anything given you ought to work, mine puts the R back in it, the others may not.

**HennieR** · 07-19-2018, 04:33 PM

BadlyspelledBouy

Since the medical aid companies determine the tariff value, would your response be any different if the rand value increase was NOT a consistent R25?
EG 84200 is R200
84201 is R256
84202 is R289.75 etc The rand values are always valid for one year. they change every January

**BadlySpelledBuoy** · 07-19-2018, 04:37 PM

If that is the case then I'd use a lookup table approach.

That will make it far easier to update when the tariffs change.

See attached. Formula in the yellow cell. Change the values in the green cells when the tariffs change.

You could use a =LOOKUP() formula to achieve the same without the list, but that way you'd have to update all the formulas each time rather than just the green cells.

BSB