Modify formula to display Null

[Gil D.]

In worksheet #1
Each row contains a unique name like: David, Joe etc. (in column A) and

it's data like: salary etc. (in columns B,C etc.). Last column contains

unique groupName like group1, group2 etc.

In worksheet #2
Each row contains a unique group name like: group1, group2 etc. (in
column A) and calculated data like: groupAverageSalary etc. (in columns

B,C etc.)

In worksheet#2 I calaculate group's data dynamic according to
user input in worksheet#1 (For example: groupAverageSalary for group1
will be calculated as the average salary of all the people who are
members in group1

I used:
=SUMIF(worksheet#1!E1:E15,worksheet#2!A1,worksheet#1!B1:B15)/COUNTIF(worksheet#1!E1:E15,worksheet#2!A1)

My problem:
In case that there no values for some group in worksheet#1 column B
I want to display NULL in worksheet#2 column B (my formula returns 0).

How can I changed my formula to do this ?

worksheet#1
A,B,... ,E
Name, salary, ..., groupName
David, 2000$, ..., group1
Joe, 1000$, ..., group2


worksheet#2
A,B, ...
groupName, averageSalary, ...
group1, 2000$, ...
group2, 1000$

 

[natanz]

try putting an if statement at the beginning, something like,
=if(SUMIF(worksheet#1!E1:E15,worksheet#2!A1,worksheet#1!B1:B15)/COUNTIF(worksheet#1!E1:E15,worksheet#2!A1)),

(SUMIF(worksheet#1!E1:E15,worksheet#2!A1,worksheet#1!B1:B15)/COUNTIF(worksheet#1!E1:E15,worksheet#2!A1)
), "NULL").

what this says is, if function = "something", then "something", else
"NULL".

my syntax may not be exactly right, but this is the idea that i have
used in the past.

 

[Gil D.]

Hello,

Thank you for your help.

My problem is that I don't know weather the sumif returns zero because
there is no value is the choosen lines or the total sum of choosen
lines values is zero.

For example:

Case 1:
group value
A 0
A

sumif which sums values for group A lines will return: 0

Case 2:
group value
A
A

sumif which sum values for group A lines will return: 0

How can I check wheather all choosen lines are empty ?

Thank you for yor help.
Gil D.