Trying to get the right formula on Parameter Code with a range and value

[curness]

Please help as spent hours trying to figure this out.

1) I've got six different bands, the first band is "Single person no children (say band A)", the second band is "Single person one children (say band B)"
2) The first two income range is if less than £100 then 90% discount, if less than £200 then 85% discount.
3) So trying to apply a formula that will give me correct discount which I can then use else where to calculate a deduction off a bill.

So far I have this =IF(A2<100,"90%",IF(A2<200,"85%",IF(A2<300,"80%",IF(A2<350,"70%",IF(A2<400,"50%",IF(A2<400,"","0"))))))

This is fine for band A but some how I wish to change the formula which will include the other bands. Not sure if a look up table is required.


Please can someone give demonstration on the first two bands with the first two discount ranges and I'll be able to hopefully adapt the rest.

many thanks

[JohnTopley]

First, remove quotes round the % values as these will be TEXT not numeric

=IF(A2<100,90%,IF(A2<200,85%,IF(A2<300,80%,IF(A2<350,70%,IF(A2<400,50%,IF(A2<400,"",0))))))

Attach a sample workbook.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[JohnTopley]

Duplicate post removed.

[curness]

Thank you for quick reply - second band would be

=IF(A2<150,90%,IF(A2<250,85%,IF(A2<350,80%,IF(A2<400,70%,IF(A2<450,50%,IF(A2<450,"",0))))))

So how do I combine the two formulas into one?

[JohnTopley]

I created 2 named ranges called "Band_A" and "Band_B" : the underscore is required as you cannot have a blank in the name.

I then used the following formula:

=VLOOKUP(F2,INDIRECT(SUBSTITUTE(G2," ","_")),2,1)

F2: income
G2: "Band A" without quotes but including blank

See examples in the attached

[curness]

Thank you very much everybody - I will play with this