Question description:
This user has given permission to use the problem statement for this
blog.
I need help fixing a Formula on Sheet 2, starting in Column G Row 22. In short, the formula should average the last six dollar amounts unless one of the last 6 dollar amounts is blank or 0. G56 on the same sheet works with the same logic but for only 3 months.
Solved by B. L. in 41 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
13/08/2018 - 05:32
Hello
Excelchat Expert
13/08/2018 - 05:32
Welcome
User
13/08/2018 - 05:32
Let me know if you have questions
[Uploaded an Excel file]
Excelchat Expert
13/08/2018 - 05:33
Thanks for the file, give me a few minutes.
Excelchat Expert
13/08/2018 - 05:37
Sorry for asking, I can't download the file from here. Would you be able to upload to google drive and share the file link?
User
13/08/2018 - 05:37
Yah, one moment
Excelchat Expert
13/08/2018 - 05:38
Thank you.
User
13/08/2018 - 05:39
https://drive.google.com/drive/u/1/folders/1IXLF9BpXtKtaNrSuoMkGNGjJe3mkQtnP
Excelchat Expert
13/08/2018 - 05:39
It seems the file is private, please share it publicly.
User
13/08/2018 - 05:40
https://drive.google.com/file/d/1FOPpI23KOHz3U_kWdkZaMg8AearVyu2e/view?usp=sharing
User
13/08/2018 - 05:40
Sorry, didn't use the sharable link
Excelchat Expert
13/08/2018 - 05:40
Thanks, I can download it now..
User
13/08/2018 - 05:41
The data that needs to be averaged are also in sheet 2 under "Raw Data"
User
13/08/2018 - 05:42
Starting at G4 and the formula should go in the area around G22
Excelchat Expert
13/08/2018 - 05:43
Alright, give me a few minutes, I will ask questions if needed.
Excelchat Expert
13/08/2018 - 05:49
Hello, are you there? I have questions.
User
13/08/2018 - 05:49
Yes
Excelchat Expert
13/08/2018 - 05:50
You need to calculate average, right? of which?
User
13/08/2018 - 05:52
The average of the last 6 dollar amounts under the Raw Data section, unless one of the data points is 0, then that point shouldn't be averaged
User
13/08/2018 - 05:53
I think I got myself
User
13/08/2018 - 05:53
=IF(SUM($F4:OFFSET(G4,,-5,,))>0,AVERAGE(B4:G4),
IF(SUM($F4:OFFSET(G4,,-4,,))>0,AVERAGE(C4:G4),IF(SUM($F4:OFFSET(G4,,-3,,))>0,AVERAGE(D4:G4),IF(SUM($F4:OFFSET(G4,,-2,,))>0,AVERAGE(E4:G4),IF(SUM(F$4:OFFSET(G4,,-1,,))>0,AVERAGE(F4:G4),G4)))))
Excelchat Expert
13/08/2018 - 05:55
You are saying last 6 dollar amount and the data range you mentioned is raw data. But raw data starts from column F, so why we are using column B in average function again?
Excelchat Expert
13/08/2018 - 05:58
Also please clear about "the last six dollar amount".
Excelchat Expert
13/08/2018 - 06:09
Hello, are you there?
Excelchat Expert
13/08/2018 - 06:10
I've analyzed the formula you gave.
Excelchat Expert
13/08/2018 - 06:11
From the look like it, you doesn't need to use OFFSET function in that formula. Simply use this..
Excelchat Expert
13/08/2018 - 06:13
=IF(SUM(B4:G4)>0,AVERAGE(B4:G4),
IF(SUM(C4:G4)>0,AVERAGE(C4:G4),IF(SUM(D4:G4)>0,AVERAGE(D4:G4),IF(SUM(E4:G4)>0,AVERAGE(E4:G4),IF(SUM(F4:G4)>0,AVERAGE(F4:G4),G4)))))
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
