SUBSET Formulae: Average of data subsets?

Attiyyah · September 11, 2012 9:32AM

Hi - I am trying to work out the average sales data for a particular sales manager. I have the Net Sales Value in one column and the Sales manager in another. I also want the average figure to be based on Business Type. How do i go about this -Thanks

tjbate · September 11, 2012 1:44PM

Attiyyah - Omniscope can do this type of calculation using Subset functions, which can be written like this for more clarity:


SUBSET_MEAN([Total Of Net Sales],

SUBSET3(

[RegionalSalesDirector],[BusinessType],[CustomerStatus],

[RegionalSalesDirector],[BusinessType],"Customer",

 "=", "=","="))

This formula is read as:
"Take the average/mean of Total of Net Sales for each subset of rows where:
1.) The Regional Sales Director is the same person
2.) The Business Type is the same type
3.) and Customer Status is only "Customer" (but not "Prospect")"

I corrected the file you sent, but you Owner-locked the file, so I could not save the changes and return to you.

Attiyyah · September 11, 2012 7:22PM

Great thanks for this! It now works

Welcome!

Categories

Ideas Parade

Tagged