Is anyone good on excel?

[PicturesOfDogs]

I thought I was okay, but this one has me stumped!

I need to work out a formula which will apply a percentage to a number based on a range.

So I have a table that will be:

0 = 5%
5 = 15%
10 = 25%
15 (and above) =30%

etc

If a value is 6 for example I will need to apply 15% to a set value (say 1000) whereas if the value is 14 I will apply 25% to the value of 1000.

Is there a way to do this?

I feel like there must be, but I can’t work it out

Thanks so much 😊

Perhaps an ‘if/then’ formula?

Google embedded if statements. Essentially you want if value <5 then 10%, otherwise if value <10 then 15% otherwise etc.

The formula evaluates each condition in order from L to R hence you don't need to use ranges.

Thank you!

Can you do if/when with ranges?

I feel like if they were singular values I could just do some sort of X-lookup, but it’s the range that’s confusing me

Great, I will have a look at that titchy!

I did try and Google, but I didn’t know what I was searching for!

I would have a table of 2 columns.1st column would be 0,1,2,3,4,5 etc
2nd column would be the value for each number so against 0,1,2,3,4 you would gave 0.05 etc.
Then a lookup formula to refer to the table, looking up your cell value snd then providing the multiplier
Then multiply your number by this

Once the formula's sorted just copy it down the whole row!

Using the IF only means your limited to one function. I think you need to use IF & AND.

I can write it in a s/s and send it over @PicturesOfDogs if that helps.

Nested IF statement.

Using one of the LOOKUP functions would be neater, but it depends whether fast and dirty is good enough.

I think XLOOKUP will do that for you on the Match part (the fifth criteria).

@DogInATent

Yes a nested if does work but i think only if you do it last number first right?

Because if you started with the
If(d2>=a1,b1,if(d2>=a2,b2,if(d2>=a3,b3 and so on excel would calculate anything more than 0 as 5% right as its the first logical function in the formula? Im not sitting in front of my laptop to test my theory and its late and im not sure my brain is working properly.

Use a lookup. (V or x) Make sure the range variable is set to TRUE. As long the % lookup source is in ascending order it’ll work fine. No need to multiple embedded if functions

DogInATent That worked perfectly, thank you SO much!! 😊

Yes, getting the order right is critical for nested IF statements, which is why the example ended up the way round it did.

Yup, that's a neater way of doing it.

Dogappreciationsociety You’re right, I changed the >= to <= and did it backwards, but it worked! Yay!

That X lookup may look neater, but as long as it functions I’m happy! Thanks so much, the screenshot was super helpful!

Double-check it works for all the values I tested. I did it the other way around and had an issue with values less than 5 the first time.

Thanks so much, it goes up to 100 so I added a final value of <=100 and have tried values at either end of the scale (1, 99 and random in between) and they all work