LinkBack URL
About LinkBacks
Is there a way to include an if then else function to an average formula that will skip cells with zero?
I went into better detail in the attached example.
Is there a way to include an if then else function to an average formula that will skip cells with zero?
I went into better detail in the attached example.
Last edited by novice2430; 06-17-2009 at 02:22 PM.
one way to achieve this, without using arrays, would be to add a helper index adjacent to your values in B, say:
You can then base your Average on the values in A such that:
A4: =IF($B4,MAX($A$3:$A3)+1,0) copy down for all rows
C4: =IF($B4,SUMIF($A$4:$A4,">="&$A4-3+($B4<>0),$B$4:$B4)/COUNTIF($A$4:$A4,">="&A4-3+($B4<>0)),0) copy down for all rows
Last edited by DonkeyOte; 06-14-2009 at 03:52 AM.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
I took this:
And made this out of it:=IF($B4,SUMIF($A$4:$A4,">="&$A4-3+($B4<>0),$B$4:$B4)/COUNTIF($A$4:$A4,">="&A4-3+($B4<>0)),0)
I notice that this adds the last two valid cells. How do I get it to subtract the last two valid cells?=IF($B4,SUMIF($A$4:$A4,">="&$A4-2+($B4<>0),$B$4:$B4),0)
Is this is a different question ? The first question implied you wanted to average (at most) the last three non-zero cells in the rolling range which was what the formula provided did (in conjunction with the helper index formulae in Column A)... what are you looking to do now exactly ?
You're right it is different, but not totally different.
I can't give an example because I'm writing from my phone, ill try to explain as clear as possible.
I have calculations in column d on the same worksheet.
The formula starts in D5 and is '=$C5-$C4. When zeros are present the calculations are thrown off,
so like before I used column a as the index and tried to use this code:
I'm trying to subtract but this code adds the two together.=IF($C4,SUMIF($A$4:$A5,">="&$A5-2+($C5<>0),$C$4:$C5),0)
Post a sample... I think you're saying you want to Sum all cells except the last two non-zero values, correct ?
I've attached the sample
Either:
D5: =IF($C5,$C5-INDEX($C$4:$C4,MATCH($A5-1,$A$4:$A4,0)),0)
copied down
or
D5: =IF($C5,$C5-LOOKUP(2,1/($C$4:$C4<>0),$C$4:$C4),0)
copied down
if you keep using the key column in A as is presently set up then use the first approach shown.
Thank you for all the help. You really know excel.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks