Calculating a score based on multiple cells

[mrbeanyuk]

Hi, its been a while!

I am trying to calculate a score for using multiple cells. In effect, I would like the following three conditions in one statement:

If the total of B1:B4 is more than 50% of the total of A1:A4 return 1
If the total of B1:B4 is more than 75% of the total of A1:A4 return 2
If the total of B1:B4 is more than 90% of the total of A1:A4 return 3

Thanks v much
Nathan

 

[Engberg]

Should be something like this then:

=IF(SUM(B1:B4)>SUM(A1:A4)*90%;3;(IF(SUM(B1:B4)>SUM(A1:A4)*75%;2;(IF(SUM(B1:B4)>SUM(A1:A4)*50%;1;0)))))

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Cubist]

What do you want to return when it's less than 50%? If that's not a concern then try:

Book1
	A	B	C
1
2	10	50	2
3	20	50
4	30	6
Sheet1

Cell Formulas
Range	Formula
C2	C2	=XMATCH(SUM(B2:B4)/SUM(A2:A4)-1,{0.5,0.75,0.9},-1)