5 Replies Latest reply on Mar 12, 2013 3:08 PM by arthi.keat

# Help with Calculations

Hi,

I am not able to figure out a couple of things in the attached workbook. Can you please help take a look...

I am trying to analyse data which shows various Consumer Numbers who can have packet losses at various times. So, Consumer 11 had a packet loss of 4 seconds with corresponding start and end time. I have come up with a few reports but I want some help formatting the results.

1. In the first tab, I am trying to show uptime of all the consumers. What I want to add here is an average of all uptime percentages at the end. Like how a grand total is displayed, I would like to have a average of all the percentages above.
2. In the second tab, I am trying to show when and what was the maximum packet loss and to which consumer. Here I would like to display only the max number for each consumer. Right now it is showing all max values for the start dates. But, I want to show only the Max for each consumer and its start datetime. So, for Consumer 202, I would like to display only March 4 2013 and 1:49:48.
3. Third tab shows total packet loss for the period. Can you please check if my calculation is correct.
4. Fourth tab I am trying to show Average packet loss by consumer. Not sure if it is correct. Can you please help check.
5. On the fifth tab, I would like to show Aggregate Uptime percentage, something like

(1-(TotalPacketLoss) / (NumberofConsumers * 7 * 24 * 60 * 60))

•      I am trying to do it weekly, so the number 7 in the above equation.

Thank you so much for your time..

Regards,

Arthi.

• ###### 1. Re: Help with Calculations

Hi Arthi,

Great work on the attached workbook.  I took a stab at your numbers and hopefully I understood your questions.

1) Did you need the total average of all consumers at the end like a Grand Total?  This was done by adding the grand total from the analysis Totals menu option.

2) Added a function of Index() and sorted by the biggest packet size.  Then only show the first one.

3) Validated and seems to be accurate

4) Created a new function that used a calculated value of Sum(Packet Size)/Count(Packet Size).  The average is then converted into a string using your formula

5) Created a new function base off of your function 1-sum([Packet Size])/(countd([Consumer#])*7*24*60*60).  The result is the same as yours.

Regards,

Son

1 of 1 people found this helpful
• ###### 2. Re: Help with Calculations

Thank you so much Son. Your solutions work perfectly for me. Appreciate your time and help in goign through the workbook and providing the solution. It is of great help !

Just one question I had on the first tab "UpTime%".

• When we do Grand Total it shows the number 96% but what I really wanted is an average which comes around 99.89%. How can we get that..

Regards,

Arthi.

• ###### 3. Re: Help with Calculations

I created a new calculated field in the workbook.  Try putting the calc "uptime" in place of "UpTime%" on the first tab.  The Grand Total should reflect 99.89% by formatting to percentage.

• ###### 4. Re: Help with Calculations

I would have never fgured it out .. Got it..thank you for your time. Appreciate it !!

Regards,

Shubha.

• ###### 5. Re: Help with Calculations

Hi Son,

I was wonderign if you have any idea on how to the solve the below thread.