Brain Freeze - What is the formula and whats the best way you would do it

Eisasuarez

Well-known Member
Joined
Mar 23, 2012
Messages
653
Hello ALL

I am trying to give my manager a true reflection of the percentage resolved vs the percentage incoming to show if we are being productive or not . Whats the best way you would do it and what formula would you use based on the scenario below.

Yesterdays Work Total: 100
Resolved Yesterday

Todays Work Total

Now based on the above there are 10 more pieces of work than yesterday

What percentage of work has been resolved and whats the percentage of work that has come in and how many.

This can then be used to compare if the percentage resolved is better than the percentage incoming however my mind has gone blank to how to get this result

Can someone be kind enough to help me

Thank You
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Eisasuarez

Well-known Member
Joined
Mar 23, 2012
Messages
653
I have not got a scooby do how to do this or if im doing it the right way but this was what I did





A1 - Volume Yesterday

B1 - 101

A2 - Volume Today
B2 - 90


<colgroup><col><col></colgroup><tbody>
</tbody>
A4 - Resolved Yesterday
B4 - 74


These are my formulas

Incoming Vol - =(VolToday-VolYesterday)+ResolvedYesterday

Percentage Incoming - =((VolToday-VolYesterday)+ResolvedYesterday)/VolYesterday

Percentage Resolved - =ResolvedYesterday/VolYesterday

Now i am not sure if these formulas are right or if this is the best way to do it

I just need to show the manager that the team have actually been productive and have done more work than whats come in overnight
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
I would do this, this way...
A​
B​
C​
1​
DateVol% resolved
2​
3/21/2016​
3​
3/22/2016​
101​
89%​
4​
3/23/2016​
90​
222%​
5​
3/24/2016​
200​
25%​
6​
3/25/2016​
50​
200%​
7​
3/26/2016​
100​
125%​
8​
3/27/2016​
125​
0%​
C3=IF(A3="","",B4/B3)
copied down
 

Eisasuarez

Well-known Member
Joined
Mar 23, 2012
Messages
653
Hi

Thank you but i think that wont work

Say the vol yday was 100 and then today it was 140 and say I resolved 0 work - That would mean that i had an extra 40 pieces of work but the formula you gave would say that i resolved 140% which is not the case

This is what I had - hopefully you or some math guru can advise the best way and the best formula to give a better statistical analysis for resolution % vs Incoming %

A1 - Volume Yesterday

B1 - 101
A2 - Volume Today
B2 - 90

<tbody>
</tbody>

A4 - Resolved Yesterday
B4 - 74


These are my formulas

Incoming Vol - =(VolToday-VolYesterday)+ResolvedYesterday

Percentage Incoming - =((VolToday-VolYesterday)+ResolvedYesterday)/VolYesterday

Percentage Resolved - =ResolvedYesterday/VolYesterday

Incoming VolPercentage ResolvedPercentage Incoming
6373.27%62.38%

<colgroup><col span="3"></colgroup><tbody>
</tbody>

I somehow think this is not correct for Percentage Incoming because say i had
100 yesterday and i completed 140 as I cleared 100 but we had another 40 come in through out the day and the following day the queue vol was at 110 - my current formula gives 150% for incoming and In vol gives 150. I would have though the Inc vol would have shown 110 and the Inc % as 110%.?

I am not sure what the best way is to get the right formula and best way to do it
 
Last edited:

Eisasuarez

Well-known Member
Joined
Mar 23, 2012
Messages
653

ADVERTISEMENT

Please can someone guide me with this

thank you
 

Eisasuarez

Well-known Member
Joined
Mar 23, 2012
Messages
653
Hi

for the work resolution, is it a case of if the work completed is more than what was in the queue yesterday, that would mean its 100% else it would be work completed/yesterdays total?

not sure how to work out the incoming and percentages or which way to go about it
 

Eisasuarez

Well-known Member
Joined
Mar 23, 2012
Messages
653

ADVERTISEMENT

How long shall i give it before bumping the thread

I really am greatful for all your help and Input. You guys are the experts and hopefully you guys can help me
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,318
Office Version
  1. 365
Platform
  1. Windows
Your problem is that's its unclear what you are asking for.
 

Eisasuarez

Well-known Member
Joined
Mar 23, 2012
Messages
653
Hi

i want to be able to show a percentage of work resolved vs what was outstanding the previous day

so if i had

101 outstanding yesterday
83 resolved yesterday

110 outstanding today

Based on this info

i need to see what work resolution % is and how many extra we have had come in today.

so 110 - 101 does not mean that only 9 came in as we resolved 83

i need to get the incoming amount and the % that has come in

is that still unclear?

please advise if you need further information
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,318
Office Version
  1. 365
Platform
  1. Windows
Well what you have had come in is 110-101+83 isn't it?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,329
Members
414,445
Latest member
walramgo02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top