Numerical value based on percentage of total spend

[JaCh]

Hello,

I am hoping someone could provide me with a solution to an excel challenge I am facing. I am trying to obtain a formula in where a cell provides me with a numerical score of say 1-5, based on what percentage the operational cost is compared to the total spend.

So if for example an entity has a operational cost of 4000 of a total spend of 1000000, that means there will be a 0,4% operational cost compared to the total spend and as such, the formula would automatically provide the designated cell with a numerical value of 1 since the categorisation illustrated here states that 0-10% operational cost should receive the value of 1.

Hopefully this is not as complicated as it appears to me.

Thank you.

 

[AlanY]

check if this works for you

Book1
	A	B	C	D	E
1	Spent	1000000		1	0.0%
2	Cost	250000		2	10.0%
3				3	20.0%
4	Score	3		4	30.0%
5				5	40.0%
Sheet1

Cell Formulas
Range	Formula
B4	B4	=LOOKUP(B2/B1,E1:E5,D1:D5)

 

[Fluff]

Hi & welcome to MrExcel.
How about

=MIN(CEILING.MATH(B8/B4,0.1)*10,5)

 

[JaCh]

I am getting some type of error stating the following: A value used in the data is the wrong data type.

This is the formula I have inserted

The percentages has been set to "percentages", not "general" or "numbers" or anything.

 

[JaCh]

Hi & welcome to MrExcel.
How about

=MIN(CEILING.MATH(B8/B4,0.1)*10,5)

Kindly excuse my lack of knowledge but I am quite uncertain as to how I should operationalise that formula. Your input is appreciated however.

 

[AlanY]

I am getting some type of error stating the following: A value used in the data is the wrong data type.

View attachment 24060

This is the formula I have inserted
View attachment 24061

The percentages has been set to "percentages", not "general" or "numbers" or anything.

the percentages should be input as .1 for 10%, and .2 for 20% etc
and format cells as percentages

 

[Fluff]

Kindly excuse my lack of knowledge but I am quite uncertain as to how I should operationalise that formula. Your input is appreciated however.

You just put it into what ever cell you want.

+Fluff v2.xlsm
	A	B	C	D
1
2
3
4	Spend	100000		1
5
6
7
8	Cost	10000
9
Clubs

Cell Formulas
Range	Formula
D4	D4	=MIN(CEILING.MATH(B8/B4,0.1)*10,5)

 

[JaCh]

Thank you @AlanY and @Fluff , based on the input I received I was able to get it to work.

 

[Fluff]

Glad we could help & thanks for the feedback.