Value '0' when apply paste link

[Guest]

Hi,

I'm using few worksheet to enter subject marks. E.g Eng-worksheet1, Maths-worksheet2 ...
At worksheet10, I insert the marks copy from wk1-wk9 using paste link.

My problem:
1) The form I design contain marks up to max 25 students (A2:A26). But currently that particular classroom only have 15 students (fill from A2:A16). Start from range B17:B26 shown 0 when I copy range e.g B2:B26 from worksheet1 and paste link at B2:B26 at worksheet10. Any way to blank that value?

2) At cell B27 (worksheet10) I want to calculate average for that subject. When I use formula =average(B2:B26) it divide by 25. Not the 15 students marks. How

3) Same goes when I want calculate min at cell B28. It calculate lowerst marks is 0.

Pls guide me. Tq

 

[Frank Kabel]

Hi
use a formula to get the values from your other sheet like the
following
=IF('other_sheet'!A1="","",'other_sheet'!A1)

This will also solve your MIN / AVERAGE problem

 

[Frank Kabel]

Hi
the functions MIN and AVERAGE ignore empty cells or cells with text.
Only numbers are counted / calculated. So this should work. Also the
formula from below should not return #VALUE. What are the exact
formulas you're using?

 

[Frank Kabel]

Hi
use the array formula (entered with cTRL+SHIFT+ENTER):
=AVERAGE(IF(B2:B26<>0,B2:B26))

 

[Peo Sjoblom]

Try

=AVERAGE(IF(B2:B26<>0,B2:B26))

entered with ctrl + shift & enter

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

Hi,
To calculate average, the formula I used is =average(B2:B26) For that

range it contain also 0 value. Any idea to Exclude 0 in calcuting average or
min.

 

[Guest]

TQ so much finally it works.

Hi
use the array formula (entered with cTRL+SHIFT+ENTER):
=AVERAGE(IF(B2:B26<>0,B2:B26))

 

[nowfal]

I NEED SAME KIND OF HELP,
I AM HAVING A PASTE SPECIAL FROM SHEET 2, IN SO MANY CELLS TO THE FIRS
SHEET. SOME TIMES SOME OF THE CELLS BECOME EMPTY IN THE SHEET 2, THE
I AM GETTING ZERO IN THE FIRST SHEET , HOW CAN I AVOID THE ZEROS,
NEED TO BE BLANK THOSE CELLS
THANKS
NOWFA

 

[Gord Dibben]

nowfal

Link on same sheet.....

=IF(A1="","",A1)

Link on other sheet.....

=IF(Sheet1!A1="","",Sheet1!A1)

Alternative.......Tools>Options>View. Uncheck "zero values"

Gord Dibben Excel MVP

 

[nowfal]

THANK YOU VERY MUCH GORD DIBBEN.
BY
NOWFA