Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DAVERAGE
I am trying to reduce a large dataset of 266 individuals of 23 species into
one that just includes the means of each species. Species names are text entries in column C. There are some missing data points, so I could not simply use: =SUMIF(Main!$C$2:$C$266,$C11,Main!FF$2:FF$266)/COUNTIF(Main!$C$2:$C$266,$C11) because the countif is not counting the exact cells going into the sum. So my current approach is to use DAVERAGE. I am specifying the worksheet(Database?), column, and criteria. The criteria compares text in the 2 columns Daverage(Main!1:65536,FF$2:FF$266,Main!$C$2:$C$266 =$C11) however, I am getting a #VALUE error. any help, thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DAVERAGE
How about something like:
=SUMPRODUCT(--(Main!$C$2:$C$266=$C11),Main!$FF$2:$FF$266)/SUMPRODUCT(--(Main!$C$2:$C$266=$C11),--(Main!$FF$2:$FF$266<"")) HTH Elkar "Andrew" wrote: I am trying to reduce a large dataset of 266 individuals of 23 species into one that just includes the means of each species. Species names are text entries in column C. There are some missing data points, so I could not simply use: =SUMIF(Main!$C$2:$C$266,$C11,Main!FF$2:FF$266)/COUNTIF(Main!$C$2:$C$266,$C11) because the countif is not counting the exact cells going into the sum. So my current approach is to use DAVERAGE. I am specifying the worksheet(Database?), column, and criteria. The criteria compares text in the 2 columns Daverage(Main!1:65536,FF$2:FF$266,Main!$C$2:$C$266 =$C11) however, I am getting a #VALUE error. any help, thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DAVERAGE
That does seem to work locally, but I can't spread it across the spreadsheet
without getting a VALUE error. I then tried removing the dollars signs from the moving cells FF, but have not quite got it yet. "Elkar" wrote: How about something like: =SUMPRODUCT(--(Main!$C$2:$C$266=$C11),Main!$FF$2:$FF$266)/SUMPRODUCT(--(Main!$C$2:$C$266=$C11),--(Main!$FF$2:$FF$266<"")) HTH Elkar "Andrew" wrote: I am trying to reduce a large dataset of 266 individuals of 23 species into one that just includes the means of each species. Species names are text entries in column C. There are some missing data points, so I could not simply use: =SUMIF(Main!$C$2:$C$266,$C11,Main!FF$2:FF$266)/COUNTIF(Main!$C$2:$C$266,$C11) because the countif is not counting the exact cells going into the sum. So my current approach is to use DAVERAGE. I am specifying the worksheet(Database?), column, and criteria. The criteria compares text in the 2 columns Daverage(Main!1:65536,FF$2:FF$266,Main!$C$2:$C$266 =$C11) however, I am getting a #VALUE error. any help, thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DAVERAGE
Try this array formula
=AVERAGE(IF(Main!$C$2:$C$266=C11,Main!$FF$2:$FF$26 6)) -- __________________________________ HTH Bob "Andrew" wrote in message ... That does seem to work locally, but I can't spread it across the spreadsheet without getting a VALUE error. I then tried removing the dollars signs from the moving cells FF, but have not quite got it yet. "Elkar" wrote: How about something like: =SUMPRODUCT(--(Main!$C$2:$C$266=$C11),Main!$FF$2:$FF$266)/SUMPRODUCT(--(Main!$C$2:$C$266=$C11),--(Main!$FF$2:$FF$266<"")) HTH Elkar "Andrew" wrote: I am trying to reduce a large dataset of 266 individuals of 23 species into one that just includes the means of each species. Species names are text entries in column C. There are some missing data points, so I could not simply use: =SUMIF(Main!$C$2:$C$266,$C11,Main!FF$2:FF$266)/COUNTIF(Main!$C$2:$C$266,$C11) because the countif is not counting the exact cells going into the sum. So my current approach is to use DAVERAGE. I am specifying the worksheet(Database?), column, and criteria. The criteria compares text in the 2 columns Daverage(Main!1:65536,FF$2:FF$266,Main!$C$2:$C$266 =$C11) however, I am getting a #VALUE error. any help, thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DAVERAGE
Hi Bob,
your formula: =AVERAGE(IF(Main!$C$2:$C$266=C11,Main!$FF$2:$FF$26 6)) returns 0, when I am expecting a nonzero return. Elkar's formula works, but only in that one cell. -Andrew "Bob Phillips" wrote: Try this array formula =AVERAGE(IF(Main!$C$2:$C$266=C11,Main!$FF$2:$FF$26 6)) -- __________________________________ HTH Bob "Andrew" wrote in message ... That does seem to work locally, but I can't spread it across the spreadsheet without getting a VALUE error. I then tried removing the dollars signs from the moving cells FF, but have not quite got it yet. "Elkar" wrote: How about something like: =SUMPRODUCT(--(Main!$C$2:$C$266=$C11),Main!$FF$2:$FF$266)/SUMPRODUCT(--(Main!$C$2:$C$266=$C11),--(Main!$FF$2:$FF$266<"")) HTH Elkar "Andrew" wrote: I am trying to reduce a large dataset of 266 individuals of 23 species into one that just includes the means of each species. Species names are text entries in column C. There are some missing data points, so I could not simply use: =SUMIF(Main!$C$2:$C$266,$C11,Main!FF$2:FF$266)/COUNTIF(Main!$C$2:$C$266,$C11) because the countif is not counting the exact cells going into the sum. So my current approach is to use DAVERAGE. I am specifying the worksheet(Database?), column, and criteria. The criteria compares text in the 2 columns Daverage(Main!1:65536,FF$2:FF$266,Main!$C$2:$C$266 =$C11) however, I am getting a #VALUE error. any help, thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DAVERAGE
Where are you moving the formula to? What data needs to change as the
formula is copied to different cells? If you provide this info, I could probably suggest a fix for the formula. "Andrew" wrote: That does seem to work locally, but I can't spread it across the spreadsheet without getting a VALUE error. I then tried removing the dollars signs from the moving cells FF, but have not quite got it yet. "Elkar" wrote: How about something like: =SUMPRODUCT(--(Main!$C$2:$C$266=$C11),Main!$FF$2:$FF$266)/SUMPRODUCT(--(Main!$C$2:$C$266=$C11),--(Main!$FF$2:$FF$266<"")) HTH Elkar "Andrew" wrote: I am trying to reduce a large dataset of 266 individuals of 23 species into one that just includes the means of each species. Species names are text entries in column C. There are some missing data points, so I could not simply use: =SUMIF(Main!$C$2:$C$266,$C11,Main!FF$2:FF$266)/COUNTIF(Main!$C$2:$C$266,$C11) because the countif is not counting the exact cells going into the sum. So my current approach is to use DAVERAGE. I am specifying the worksheet(Database?), column, and criteria. The criteria compares text in the 2 columns Daverage(Main!1:65536,FF$2:FF$266,Main!$C$2:$C$266 =$C11) however, I am getting a #VALUE error. any help, thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DAVERAGE
Hi Elkar, I am copying the formula across both columns and rows of the current worksheet. My goal of the current worksheet is to generate species mean values from the "Main" worksheet. Hence column FF will need to change to FG, FH, etc as it is copied, but row number should not change. I am away from work right now, but I think I tried this and it gave me VALUE error. "Elkar" wrote: Where are you moving the formula to? What data needs to change as the formula is copied to different cells? If you provide this info, I could probably suggest a fix for the formula. "Andrew" wrote: That does seem to work locally, but I can't spread it across the spreadsheet without getting a VALUE error. I then tried removing the dollars signs from the moving cells FF, but have not quite got it yet. "Elkar" wrote: How about something like: =SUMPRODUCT(--(Main!$C$2:$C$266=$C11),Main!$FF$2:$FF$266)/SUMPRODUCT(--(Main!$C$2:$C$266=$C11),--(Main!$FF$2:$FF$266<"")) HTH Elkar "Andrew" wrote: I am trying to reduce a large dataset of 266 individuals of 23 species into one that just includes the means of each species. Species names are text entries in column C. There are some missing data points, so I could not simply use: =SUMIF(Main!$C$2:$C$266,$C11,Main!FF$2:FF$266)/COUNTIF(Main!$C$2:$C$266,$C11) because the countif is not counting the exact cells going into the sum. So my current approach is to use DAVERAGE. I am specifying the worksheet(Database?), column, and criteria. The criteria compares text in the 2 columns Daverage(Main!1:65536,FF$2:FF$266,Main!$C$2:$C$266 =$C11) however, I am getting a #VALUE error. any help, thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DAVERAGE
What about cell C11? Would that need to change to C12, C13 etc when copied
down? If not, try making that absolute $C$11. HTH Elkar "Andrew" wrote: Hi Elkar, I am copying the formula across both columns and rows of the current worksheet. My goal of the current worksheet is to generate species mean values from the "Main" worksheet. Hence column FF will need to change to FG, FH, etc as it is copied, but row number should not change. I am away from work right now, but I think I tried this and it gave me VALUE error. "Elkar" wrote: Where are you moving the formula to? What data needs to change as the formula is copied to different cells? If you provide this info, I could probably suggest a fix for the formula. "Andrew" wrote: That does seem to work locally, but I can't spread it across the spreadsheet without getting a VALUE error. I then tried removing the dollars signs from the moving cells FF, but have not quite got it yet. "Elkar" wrote: How about something like: =SUMPRODUCT(--(Main!$C$2:$C$266=$C11),Main!$FF$2:$FF$266)/SUMPRODUCT(--(Main!$C$2:$C$266=$C11),--(Main!$FF$2:$FF$266<"")) HTH Elkar "Andrew" wrote: I am trying to reduce a large dataset of 266 individuals of 23 species into one that just includes the means of each species. Species names are text entries in column C. There are some missing data points, so I could not simply use: =SUMIF(Main!$C$2:$C$266,$C11,Main!FF$2:FF$266)/COUNTIF(Main!$C$2:$C$266,$C11) because the countif is not counting the exact cells going into the sum. So my current approach is to use DAVERAGE. I am specifying the worksheet(Database?), column, and criteria. The criteria compares text in the 2 columns Daverage(Main!1:65536,FF$2:FF$266,Main!$C$2:$C$266 =$C11) however, I am getting a #VALUE error. any help, thanks |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DAVERAGE
Hi,
There is some problem in the criteria of your formula. The criteria has to be a range of cells and not an equation such as what you have used (Main!$C$2:$C$266=$C11). Also please ensure that the headers of the range and criteria are the same. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Andrew" wrote in message ... I am trying to reduce a large dataset of 266 individuals of 23 species into one that just includes the means of each species. Species names are text entries in column C. There are some missing data points, so I could not simply use: =SUMIF(Main!$C$2:$C$266,$C11,Main!FF$2:FF$266)/COUNTIF(Main!$C$2:$C$266,$C11) because the countif is not counting the exact cells going into the sum. So my current approach is to use DAVERAGE. I am specifying the worksheet(Database?), column, and criteria. The criteria compares text in the 2 columns Daverage(Main!1:65536,FF$2:FF$266,Main!$C$2:$C$266 =$C11) however, I am getting a #VALUE error. any help, thanks |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DAVERAGE
Did you array-enter it?
-- __________________________________ HTH Bob "Andrew" wrote in message ... Hi Bob, your formula: =AVERAGE(IF(Main!$C$2:$C$266=C11,Main!$FF$2:$FF$26 6)) returns 0, when I am expecting a nonzero return. Elkar's formula works, but only in that one cell. -Andrew "Bob Phillips" wrote: Try this array formula =AVERAGE(IF(Main!$C$2:$C$266=C11,Main!$FF$2:$FF$26 6)) -- __________________________________ HTH Bob "Andrew" wrote in message ... That does seem to work locally, but I can't spread it across the spreadsheet without getting a VALUE error. I then tried removing the dollars signs from the moving cells FF, but have not quite got it yet. "Elkar" wrote: How about something like: =SUMPRODUCT(--(Main!$C$2:$C$266=$C11),Main!$FF$2:$FF$266)/SUMPRODUCT(--(Main!$C$2:$C$266=$C11),--(Main!$FF$2:$FF$266<"")) HTH Elkar "Andrew" wrote: I am trying to reduce a large dataset of 266 individuals of 23 species into one that just includes the means of each species. Species names are text entries in column C. There are some missing data points, so I could not simply use: =SUMIF(Main!$C$2:$C$266,$C11,Main!FF$2:FF$266)/COUNTIF(Main!$C$2:$C$266,$C11) because the countif is not counting the exact cells going into the sum. So my current approach is to use DAVERAGE. I am specifying the worksheet(Database?), column, and criteria. The criteria compares text in the 2 columns Daverage(Main!1:65536,FF$2:FF$266,Main!$C$2:$C$266 =$C11) however, I am getting a #VALUE error. any help, thanks |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DAVERAGE
Elkthar formula is very close, and does copy across cells now , however there
are some slight discrepancies in the mean values it returns. Also, for many columns it returns VALUE error. I think the two arrays are not always the same size, and thus it can't multiply those arrays. =SUMPRODUCT(--(Main!$C$2:$C$266=$C8),Main!AZ$2:AZ$266)/SUMPRODUCT(--(Main!$C$2:$C$266=$C8),--(Main!AZ$2:AZ$266<"")) Ashish, how would I apply the condition if not in the criteria of DAVERAGE? "Ashish Mathur" wrote: Hi, There is some problem in the criteria of your formula. The criteria has to be a range of cells and not an equation such as what you have used (Main!$C$2:$C$266=$C11). Also please ensure that the headers of the range and criteria are the same. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Andrew" wrote in message ... I am trying to reduce a large dataset of 266 individuals of 23 species into one that just includes the means of each species. Species names are text entries in column C. There are some missing data points, so I could not simply use: =SUMIF(Main!$C$2:$C$266,$C11,Main!FF$2:FF$266)/COUNTIF(Main!$C$2:$C$266,$C11) because the countif is not counting the exact cells going into the sum. So my current approach is to use DAVERAGE. I am specifying the worksheet(Database?), column, and criteria. The criteria compares text in the 2 columns Daverage(Main!1:65536,FF$2:FF$266,Main!$C$2:$C$266 =$C11) however, I am getting a #VALUE error. any help, thanks |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
handling missing data
One of the problems I have is that for some of the cells in AZ(or other
moving data columns) return #VALUE, which means that the formula which calls for those cells also returns #VALUE. Any global settings in Excell that let one handle missing data efficiently? It is getting tedious to be constantly entering formulas to make blanks. for example: =IF(OR(ISBLANK(DL21),ISBLANK(AF21)),"",DL21/AF21) "Andrew" wrote: Elkthar formula is very close, and does copy across cells now , however there are some slight discrepancies in the mean values it returns. Also, for many columns it returns VALUE error. I think the two arrays are not always the same size, and thus it can't multiply those arrays. =SUMPRODUCT(--(Main!$C$2:$C$266=$C8),Main!AZ$2:AZ$266)/SUMPRODUCT(--(Main!$C$2:$C$266=$C8),--(Main!AZ$2:AZ$266<"")) Ashish, how would I apply the condition if not in the criteria of DAVERAGE? "Ashish Mathur" wrote: Hi, There is some problem in the criteria of your formula. The criteria has to be a range of cells and not an equation such as what you have used (Main!$C$2:$C$266=$C11). Also please ensure that the headers of the range and criteria are the same. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Andrew" wrote in message ... I am trying to reduce a large dataset of 266 individuals of 23 species into one that just includes the means of each species. Species names are text entries in column C. There are some missing data points, so I could not simply use: =SUMIF(Main!$C$2:$C$266,$C11,Main!FF$2:FF$266)/COUNTIF(Main!$C$2:$C$266,$C11) because the countif is not counting the exact cells going into the sum. So my current approach is to use DAVERAGE. I am specifying the worksheet(Database?), column, and criteria. The criteria compares text in the 2 columns Daverage(Main!1:65536,FF$2:FF$266,Main!$C$2:$C$266 =$C11) however, I am getting a #VALUE error. any help, thanks |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
handling missing data
You may be better changing your
=IF(OR(ISBLANK(DL21),ISBLANK(AF21)),"",DL21/AF21) to =IF(OR(DL21="",AF21=""),"",DL21/AF21) ISBLANK will not be true for cells containing a formula that results in "". -- David Biddulph "Andrew" wrote in message ... One of the problems I have is that for some of the cells in AZ(or other moving data columns) return #VALUE, which means that the formula which calls for those cells also returns #VALUE. Any global settings in Excell that let one handle missing data efficiently? It is getting tedious to be constantly entering formulas to make blanks. for example: =IF(OR(ISBLANK(DL21),ISBLANK(AF21)),"",DL21/AF21) "Andrew" wrote: Elkthar formula is very close, and does copy across cells now , however there are some slight discrepancies in the mean values it returns. Also, for many columns it returns VALUE error. I think the two arrays are not always the same size, and thus it can't multiply those arrays. =SUMPRODUCT(--(Main!$C$2:$C$266=$C8),Main!AZ$2:AZ$266)/SUMPRODUCT(--(Main!$C$2:$C$266=$C8),--(Main!AZ$2:AZ$266<"")) Ashish, how would I apply the condition if not in the criteria of DAVERAGE? "Ashish Mathur" wrote: Hi, There is some problem in the criteria of your formula. The criteria has to be a range of cells and not an equation such as what you have used (Main!$C$2:$C$266=$C11). Also please ensure that the headers of the range and criteria are the same. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Andrew" wrote in message ... I am trying to reduce a large dataset of 266 individuals of 23 species into one that just includes the means of each species. Species names are text entries in column C. There are some missing data points, so I could not simply use: =SUMIF(Main!$C$2:$C$266,$C11,Main!FF$2:FF$266)/COUNTIF(Main!$C$2:$C$266,$C11) because the countif is not counting the exact cells going into the sum. So my current approach is to use DAVERAGE. I am specifying the worksheet(Database?), column, and criteria. The criteria compares text in the 2 columns Daverage(Main!1:65536,FF$2:FF$266,Main!$C$2:$C$266 =$C11) however, I am getting a #VALUE error. any help, thanks |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
handling missing data
Here is the current formula for a cell:
=SUMPRODUCT(--(Main!$C$2:$C$266=$C2),Main!I$2:I$266)/SUMPRODUCT(--(Main!$C$2:$C$266=$C2),--(Main!I$2:I$266<"")) The problem is that when it finds DIV/0 or #VALUE errors anywhere in the column it is searching, it then returns a #VALUE error. I would like to make it more robust and able to still give a numeric return despite errors in some of the cells it is searching. In the numerator of the above formula, maybe instead of the criteria just "" also have 'not ERROR' or something like that. any ideas how to specify that? thanks |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
handling missing data
Its really best to do your Error Handling at the source. Whichever formulas
are generating the #VALUE errors (or any error for that matter) should be re-written to allow for this. Such as: =IF(ISERROR(your forumla),"Error",your formula) This will allow you to still be notified when an error occurs, but will also allow Excel to continue to perform calculations on your results since they're now simple text values rather than an usuable error. But, if you really want bypass this advice, you might be able to get away with something like: =SUMPRODUCT(--(IF(ISERROR(Main!$C$2:$C$266),0,Main!$C$2:$C$266)= $C2),--(IF(ISERROR(Main!I$2:I$266),0,Main!I$2:I$266))/SUMPRODUCT(--(IF(ISERROR(Main!$C$2:$C$266),0,Main!$C$2:$C$266)= $C2),--(IF(ISERROR(Main!I$2:I$266),0,Main!I$2:I$266)<"") )) This would be entered as an ARRAY formula, so press CTRL-SHIFT-ENTER instead of just ENTER. HTH Elkar "Andrew" wrote: Here is the current formula for a cell: =SUMPRODUCT(--(Main!$C$2:$C$266=$C2),Main!I$2:I$266)/SUMPRODUCT(--(Main!$C$2:$C$266=$C2),--(Main!I$2:I$266<"")) The problem is that when it finds DIV/0 or #VALUE errors anywhere in the column it is searching, it then returns a #VALUE error. I would like to make it more robust and able to still give a numeric return despite errors in some of the cells it is searching. In the numerator of the above formula, maybe instead of the criteria just "" also have 'not ERROR' or something like that. any ideas how to specify that? thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DAVERAGE | Excel Discussion (Misc queries) | |||
daverage | Excel Worksheet Functions | |||
DAVERAGE criteria HELP!!!! | Excel Discussion (Misc queries) | |||
Daverage | Excel Discussion (Misc queries) | |||
daverage problems | Excel Worksheet Functions |