Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT
Hello,
I was looking for some help on one of the formula I had used for generating some report. Using SUMPRODUCT from various parameters I was getting average of data entered in the cells TAT!$E$3:$E$1000. But few of my cells would be either "blank" or "zero", in which case those cells should not be considered. The avarage for these cells (TAT!$E$3:$E$1000) are calculated based on an input data in cell B10. and that input data is compared in TAT!$B$3:$B $1000 My question is, though B10 data appears in TAT!$B$3:$B$1000, if the corresponding cells (E3 to E1000) is either "blank" or "zero" then those cells should be ignored. For Eg:- Name Data a 15 b 25 c 20 a a 42 a In the give data though a is appering 4 times, data is appearing only two times in corresponding cell. So average should be (15+42)/2 AND NOT (15+42)/4 Assistance would be of great help!!! This is the formula I have used:- SUMPRODUCT((TAT!$B$3:$E$1000=Consolidated!$B10)*(T AT!$E$3:$E$1000)* (TEXT(TAT!$C$3:$C$1000,"mmm")=Consolidated!$B$2)*( YEAR(TAT!$C$3:$C $1000)=2009))/SUMPRODUCT((TAT!$B$3:$E$1000=Consolidated!$B10)*(T EXT (TAT!$C$3:$C$1000,"mmm")=Consolidated!$B$2)*(YEAR( TAT!$C$3:$C$1000) =2009)) Thanks in advance!!! Srikanth |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT
Hi Shrikanth,
Iif you are looking to get the avarage based on your condition, assuming that your data is in A1:B6 =AVERAGEIFS(B1:B6,B1:B6,"0",A1:A6,"=a") will give you 28.5 Thanks, Abdul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
Sumproduct | Excel Discussion (Misc queries) | |||
sumproduct help | Excel Discussion (Misc queries) | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
sumproduct | Excel Discussion (Misc queries) |