weefatb0b
New Member
- Joined
- Nov 17, 2022
- Messages
- 28
- Office Version
- 2013
- Platform
- Windows
Hi
Sorry I cannot upload a sample spreadsheet, so I am hoping this make sense?
I have been tasked with averaging payments over the last 52 out of 104 weeks all of my value cells have been formatted to number with 2 decimal places
I have tried
IFERROR(AVERAGEIF(B3:DA3,"<>0"),"") and get the average per week for the 104 weeks ignoring any 0.00 values. Which calculates correctly for any that have under 52 weeks of values greater than 0.00
But if the count of the weeks are greater than 52, which I have counted in a separate cell (DC3), I need to average the first 52 weeks, that have a value greater than 0.00, which may not necessarily be week 1 to 52
For this I have used
IFERROR(AVERAGEIF(DC3,">=52",B3:BA3),DB3)
Which should be if dc3 is greater than 52, average the first 52 cells, B3:BA3 else return the value in DB3 which is a higher total than the 1st formula.
I have then tried
IFERROR(AVERAGEA(B3:BA3,DC3>=$DJ$5),DB3)
Which gives a higher total than formula 1 but a lower total than formula 2
And if I use
AVERAGE(B3:BA3)
I get a different total again which matches the first 52 weeks in my lookup table.
I guess what I am asking is which one is correct, if any or is it actually possible to do what I am being asked?
Thanks in advance
Sorry I cannot upload a sample spreadsheet, so I am hoping this make sense?
I have been tasked with averaging payments over the last 52 out of 104 weeks all of my value cells have been formatted to number with 2 decimal places
I have tried
IFERROR(AVERAGEIF(B3:DA3,"<>0"),"") and get the average per week for the 104 weeks ignoring any 0.00 values. Which calculates correctly for any that have under 52 weeks of values greater than 0.00
But if the count of the weeks are greater than 52, which I have counted in a separate cell (DC3), I need to average the first 52 weeks, that have a value greater than 0.00, which may not necessarily be week 1 to 52
For this I have used
IFERROR(AVERAGEIF(DC3,">=52",B3:BA3),DB3)
Which should be if dc3 is greater than 52, average the first 52 cells, B3:BA3 else return the value in DB3 which is a higher total than the 1st formula.
I have then tried
IFERROR(AVERAGEA(B3:BA3,DC3>=$DJ$5),DB3)
Which gives a higher total than formula 1 but a lower total than formula 2
And if I use
AVERAGE(B3:BA3)
I get a different total again which matches the first 52 weeks in my lookup table.
I guess what I am asking is which one is correct, if any or is it actually possible to do what I am being asked?
Thanks in advance