How to count the optimal number?

[areq09]

Hi,

I have this file: Excel. There are brand ratings and the average rating for each brand. I want to count how many “5” ratings are needed for each brand so that the average of each of them is at least 4.

Do you have an idea how to do it (the solution can be for both Excel and Google Sheets)?

 

[Toadstool]

Hi Areq09,

Try =COUNTIF(A:A,D2)*(4-E2)

 

[mikerickson]

Try
=4*COUNTIF(A:A,D2)-SUMIF(A:A,D2,B:B)

 

[areq09]

Thank you. Although I get errors in both answers that you provided. Is it possible to solve it with Excel though? Do you think that Solver can address this kind of calculation?

 

[Peter_SSs]

Although I get errors in both answers that you provided.

Can you provide a small set of sample data (preferably with XL2BB) where those formulas produced errors?
They seem to produce the correct results for your sample data. Or have we all misunderstood the requirement?

Excel.xlsx
	A	B	C	D	E	F	G
1	Brand	Rating		Brand	Average Brand Rating	Toadstool	mikerickson
2	A	1		A	2.6	11	11
3	B	1		B	3.0	8	8
4	C	1		C	3.4	5	5
5	A	2
6	B	2
7	C	2
8	A	3
9	B	3
10	C	3
11	A	4
12	B	4
13	C	4
14	A	5
15	B	5
16	C	5
17	A	1
18	B	2
19	C	3
20	A	2
21	B	3
22	C	4
23	A	3
24	B	4
25	C	5
26
Arkusz1

Cell Formulas
Range	Formula
E2:E4	E2	=AVERAGEIFS($B:$B,$A:$A,$D2)
F2:F4	F2	=COUNTIF(A:A,D2)*(4-E2)
G2:G4	G2	=4*COUNTIF(A:A,D2)-SUMIF(A:A,D2,B:B)

 

[mikerickson]

Thank you. Although I get errors in both answers that you provided. Is it possible to solve it with Excel though? Do you think that Solver can address this kind of calculation?

The question of now many rating of 5 will it take to increase and average to a given number isn't really an Excel question, its algebra.

 

[areq09]

My apologies, it's working now. Thank you!