I need to calculate the Average star review and the target star review in excel

[Globalcrossways4]

Site	1Star	2Star	3Star	4Star	5Star	Avg.	Target Avg.	# of stars needed
						#DIV/0!​		0​
						#DIV/0!​		0​
						#DIV/0!​		0​
						#DIV/0!​		0​
						#DIV/0!​		0​
						#DIV/0!​		0​
						#DIV/0!​		0​

The formula inside of cell g is :=((B2 * 1) + (C2 * 2) + (D2 *3) + (E2 * 4) + (F2 * 5) + (I2 *5)) / (B2 + C2 + D2 + E2 + F2 +I2)
I need the formula for the Target average to complete the cycle and get the results
The Formula inside cell I is: =ROUNDUP((H2*SUM($B2:$F2)-(B2*1+C2*2+D2*3+E2*4+F2*5))/(5-H2),0)

 

[etaf]

you will get a div error if blank
What actually gets put into the cells ?,

=((B2 * 1) + (C2 * 2) + (D2 *3) + (E2 * 4) + (F2 * 5) + (I2 *5)) / (B2 + C2 + D2 + E2 + F2 +I2)
IFERROR ( ((B2 * 1) + (C2 * 2) + (D2 *3) + (E2 * 4) + (F2 * 5) + (I2 *5)) / (B2 + C2 + D2 + E2 + F2 +I2) , "")

can you post using XL2BB - see menu/signature

 

[Globalcrossways4]

you will get a div error if blank
What actually gets put into the cells ?,

=((B2 * 1) + (C2 * 2) + (D2 *3) + (E2 * 4) + (F2 * 5) + (I2 *5)) / (B2 + C2 + D2 + E2 + F2 +I2)
IFERROR ( ((B2 * 1) + (C2 * 2) + (D2 *3) + (E2 * 4) + (F2 * 5) + (I2 *5)) / (B2 + C2 + D2 + E2 + F2 +I2) , "")

can you post using XL2BB - see menu/signature

I wish I was able to use the addon XL2BB but my system administrator will not authorize any add-ons. In the cells I enter the number of each type of review from each website. example:

Site	1Star	2Star	3Star	4Star	5Star	Avg.	Target Avg.	# of stars needed
Google	35​	242​	12​	950​	2745​	4.538153​		-3616​
						#DIV/0!​		0​
						#DIV/0!​		0​
						#DIV/0!​		0​
						#DIV/0!​		0​
						#DIV/0!​		0​
						#DIV/0!​		0​

 

[Joe4]

Here is a shorter formula you can use for the average, assuming that "1Star" starts in column B:

=IFERROR(SUMPRODUCT(--(B2:F2*(COLUMN(B2:F2)-1)))/SUM(B2:F2),"")

 

[Globalcrossways4]

you will get a div error if blank
What actually gets put into the cells ?,

=((B2 * 1) + (C2 * 2) + (D2 *3) + (E2 * 4) + (F2 * 5) + (I2 *5)) / (B2 + C2 + D2 + E2 + F2 +I2)
IFERROR ( ((B2 * 1) + (C2 * 2) + (D2 *3) + (E2 * 4) + (F2 * 5) + (I2 *5)) / (B2 + C2 + D2 + E2 + F2 +I2) , "")

can you post using XL2BB - see menu/signature

1Star	2Star	3Star	4Star	5Star	Avg.	Target Avg.	# of stars needed
423	30	108	167	991	3.74055		-1286
18	2	0	9	64	4.06452		-76
47	4	6	22	175	4.07874		-208
20	1	1	6	36	3.57813		-46
292	9	26	43	189	2.69231		-301
9	0	0	1	5	2.53333		-8
25	3	1	7	18	2.81481		-31
12	0	0	6	21	3.61538		-29
2	0	0	0	3	3.4		-4
0	0	0	0	0	#DIV/0!		0
179	45	150	412	979	4.11445		-1453
112	17	61	186	685	4.2394		-900
266	25	34	93	308	3.20937		-466
9	2	8	18	26	3.79365		-48
69	5	27	31	188	3.825		-245
7	3	1	0	19	3.7		-23
0	0	0	1	29	4.96667		-30
2	0	0	3	54	4.81356		-57

This is an actual copy of the spreadsheet I am trying to create. Not sure if it will be of any help. Sorry all.

 

[etaf]

the sumproduct provides the average as posted by Joe4
No. of stars needed , what star is that based on 3star number needed,

you have NO target , so The Formula inside cell I is: =ROUNDUP((H2*SUM($B2:$F2)-(B2*1+C2*2+D2*3+E2*4+F2*5))/(5-H2),0)

would not work
so not sure what the question is now

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1		1Star	2Star	3Star	4Star	5Star	Avg.	Target Avg.	# of stars needed		Average	Stars
2		423	30	108	167	991	3.74055		-1286		3.74054683	-1286
3		18	2	0	9	64	4.06452		-76		4.06451613	-76
4		47	4	6	22	175	4.07874		-208		4.07874016	-208
5		20	1	1	6	36	3.57813		-46		3.578125	-46
6		292	9	26	43	189	2.69231		-301		2.69230769	-301
7		9	0	0	1	5	2.53333		-8		2.53333333	-8
8		25	3	1	7	18	2.81481		-31		2.81481481	-31
9		12	0	0	6	21	3.61538		-29		3.61538462	-29
10		2	0	0	0	3	3.4		-4		3.4	-4
11		0	0	0	0	0	#DIV/0!		0			0
12		179	45	150	412	979	4.11445		-1453		4.11444759	-1453
13		112	17	61	186	685	4.2394		-900		4.2393968	-900
14		266	25	34	93	308	3.20937		-466		3.20936639	-466
15		9	2	8	18	26	3.79365		-48		3.79365079	-48
16		69	5	27	31	188	3.825		-245		3.825	-245
17		7	3	1	0	19	3.7		-23		3.7	-23
18		0	0	0	1	29	4.96667		-30		4.96666667	-30
19		2	0	0	3	54	4.81356		-57		4.81355932	-57
Sheet1

Cell Formulas
Range	Formula
K2:K19	K2	=IFERROR(SUMPRODUCT(--(B2:F2*(COLUMN(B2:F2)-1)))/SUM(B2:F2),"")
L2:L19	L2	=ROUNDUP((H2*SUM($B2:$F2)-(B2*1+C2*2+D2*3+E2*4+F2*5))/(5-H2),0)

 

[Globalcrossways4]

the sumproduct provides the average as posted by Joe4
No. of stars needed , what star is that based on 3star number needed,

would not work
so not sure what the question is now

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1		1Star	2Star	3Star	4Star	5Star	Avg.	Target Avg.	# of stars needed		Average	Stars
2		423	30	108	167	991	3.74055		-1286		3.74054683	-1286
3		18	2	0	9	64	4.06452		-76		4.06451613	-76
4		47	4	6	22	175	4.07874		-208		4.07874016	-208
5		20	1	1	6	36	3.57813		-46		3.578125	-46
6		292	9	26	43	189	2.69231		-301		2.69230769	-301
7		9	0	0	1	5	2.53333		-8		2.53333333	-8
8		25	3	1	7	18	2.81481		-31		2.81481481	-31
9		12	0	0	6	21	3.61538		-29		3.61538462	-29
10		2	0	0	0	3	3.4		-4		3.4	-4
11		0	0	0	0	0	#DIV/0!		0			0
12		179	45	150	412	979	4.11445		-1453		4.11444759	-1453
13		112	17	61	186	685	4.2394		-900		4.2393968	-900
14		266	25	34	93	308	3.20937		-466		3.20936639	-466
15		9	2	8	18	26	3.79365		-48		3.79365079	-48
16		69	5	27	31	188	3.825		-245		3.825	-245
17		7	3	1	0	19	3.7		-23		3.7	-23
18		0	0	0	1	29	4.96667		-30		4.96666667	-30
19		2	0	0	3	54	4.81356		-57		4.81355932	-57
Sheet1

Cell Formulas
Range	Formula
K2:K19	K2	=IFERROR(SUMPRODUCT(--(B2:F2*(COLUMN(B2:F2)-1)))/SUM(B2:F2),"")
L2:L19	L2	=ROUNDUP((H2*SUM($B2:$F2)-(B2*1+C2*2+D2*3+E2*4+F2*5))/(5-H2),0)

The target needed is 5 stars

 

[etaf]

to get 5 star rating would need all stars to be 5
if you had just 1 , one star
and
100,000,000,000 5stars
that will still get an average of
4.99999999996
You'll never reach 5 stars unless you dont get any of the other stars , so not sure how your
Stars needed will work to achieve the maximum rating