Average of cells with rept formula

**Corvisius** · 09-08-2012, 11:35 PM

Hi...

I'm... VERY... new to excel

Problem is:

I have cells repeating the values from another sheet (is there a better option than rept?):
| B | C
1|
2| =REPT('Sheet1'!G43, 1) | =REPT('Sheet1'!H43, 1)

which returns:

| B | C
1|
2| 90 | 95

and then I try:

=AVERAGE(B2:C2)

and I just get #DIV/0.

Why?

Is it trying to calculate the underlying formula rather than the value?

**djw2000** · 09-08-2012, 11:56 PM

Replace =REPT('Sheet1'!G43, 1) with ='Sheet1'!G43

I'm not 100% sure that will fix your problem but it works for me.

**vlady** · 09-09-2012, 01:30 AM

or another way is the use of double negative

***For numbers only
=--REPT(A1,2)
or by multiplication
=REPT(A1,2)*1

**Corvisius** · 09-09-2012, 02:29 PM

Thanks for the responses

Unfortunately, they didn't work =\

djw, I'm still getting a div/0 error after removing the rept function.
The correct number is displayed, just not calculated in the average.

95 95 #DIV/0!

=sheet!G43 =sheet!G44 =AVERAGE(B2:C2)

vlady, I tried adding double negative before the REPT, but I just get a #value error. and same thing with *1.

#VALUE! 95 #VALUE!

=--REPT(sheet!G43,1) =sheet!G44 =AVERAGE(B2:C2)