**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.*