Formula

Resolved · Urgent Priority · Version 2010

Mon 2 Dec 2013, 09:29 Edited on Mon 2 Dec 2013, 09:35 Delegate Astrid said...

Astrid has attended:
Excel Advanced - Formulas & Functions course

Formula

I have calculated the average % of business per weekday as follows:

Monday 12%
Tuesday 20%
Wednesday18%
Thursday 16%
Friday 13%
Saturday 11%
Sunday 10%

I have a total for each week, eg. Monday 6th Jan to Sunday 12th Jan the total is 3138 (100%).

How do I write a formula to calculate the % for each day against the total of the week?

Thank you!

PS: I can do a simple mulitplication for each week but was wondering if there's a formula I can use to populate the figures for the whole year as I have so many to do...

Mon 2 Dec 2013, 16:06 Trainer Gary said...

RE: Formula

Hi Astrid

Thanks for getting in touch. I think I'm missing something - have you not already calculated what the % of the week's total is? e.g. 12% is Monday's percentage?

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Mon 2 Dec 2013, 16:11 Delegate Astrid said...

RE: Formula

Hi Gary,

Yes, I already have the % averages for each day (as for this example)
Monday 12%
Tuesday 20%
Wednesday18%
Thursday 16%
Friday 13%
Saturday 11%
Sunday 10%

I also have a total for each week.

Problem is that there is a different total for each week so I can't see how I can do a formula to copy through all 52 weeks? It seems I have to absolute each total per week?

Tue 3 Dec 2013, 22:09 Trainer Gary said...

RE: Formula

Hi Astrid

I guess this depends on the layout of your data. If your data is in uninterrupted blocks you'll be able to drag the formula across.

Remember how we looked at 'mixed' references? e.g. you can have E5, $E$5, $E5 or E$5. Does this help?

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Wed 4 Dec 2013, 11:29 Delegate Astrid said...

RE: Formula

Doh! Didn't think of that.

Thanks Gary,
Astrid

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Hiding Formulae in the Formula Bar

It is possible to protect the contents of a cell reference(s) from amendment by applying cell protection. The contents of the cell reference can also be stopped from displaying in the formula bar.

Step 1: Select Format > Cells > Protection.

Step 2: Tick Hidden option. Ensure Locked is ticked

Step 3: Select Tools > Protection > Protect Sheet

(Ensure "Protect worksheet and contents of locked cells" is ticked)

View all Excel hints and tips