AVERAGE and SUM Formula HELP

**pphg** · 11-16-2012, 12:39 AM

Good day!

Hope you can help me guys. I have attached a spreadsheet that have data from Jan 1- Dec 31. I basically need to have a seperate report sheet that shows the average calls received but only for Monday to Friday. However, there is an input parameter for reporting week and reporting month and as such the numbers automatically add Saturday and Sunday too.

You may view the attached files for reference.

Thanks in advance for the help. Im not really good with formulas haha

regards

**pphg** · 11-16-2012, 12:50 AM

And just to add, the average should be Weekly, last week, last month, and history ( from day 1 to now)

**pphg** · 11-16-2012, 03:37 AM

up....................

**Pepe Le Mokko** · 11-16-2012, 03:51 AM

Forum rules suggest you wait 24 hrs before bumps. If it's urgent please contact pay sites

**crzu** · 11-16-2012, 04:58 AM

@ pphg

Sorry to say. Your request is very heavy and time consuming. It is as if to build a whole report sheet instead of solving a formula.

Probably if you could consider to try to solve some of the minor problem and proceed with key formula that you could not solve.

Google would be a good way to find the simple formula you need for your request. I could advice you to look at formula like "Weekday", "Day", "Date", "Month", "Year", "Today", and "Text" etc.

**icestationzbra** · 11-16-2012, 01:34 PM

see if these help. all are array formulae. Saturdays and Sundays are being disregarded.

on Dashboard sheet:

C11:

Please Login or Register  to view this content.

D11:

Please Login or Register  to view this content.

E11:

Please Login or Register  to view this content.

adapt these to generate other formulae in row 12 onwards on Dashboard sheet.

EDIT: for cell F11, includes requirements from post #7 below.

Please Login or Register  to view this content.

**pphg** · 11-17-2012, 02:22 PM

Thanks Ice! Great help, this is exactly what I needed

However, can I request to make adjustment for the formula provided for cell F11. Is it possible to get the average from August to last post date (in this case, the attached spreadsheet last post date is 26 Oct since we only take weekdays). So basically, average from Aug 1 to the current reporting week in the parameter tab. And also not include in the average cells that has "0" value.

Many thanks again for the great help!