Ratio label

**akhlaq768** · 01-29-2008, 12:49 PM

I’ve been asked a question which I feel is impossible but I’ve I believe someone here would be able to help me.

Firstly I need to work out the ratio between to numbers (New and Follow-up)

Once I have worked that out, I need to plot it on the Pivot Chart.

I’ve explored all the avenue I know about, hope someone can help

**jevni1974** · 01-29-2008, 04:05 PM

hi there,

can you post a file with sample data with a more detailed explanation about what you are trying to achieve. The problem you posted is quite highlevel.

Thanks

**akhlaq768** · 01-31-2008, 06:29 AM

Hi,

Sorry for the late reply.

I hope i can make this clear.... I’m going to break it down as much as i can.

Firstly in the worksheet called "Pivot", there is a pivot table, which shows the appointment type, which can be either "New" or "Follow Up" for each month of the year.

Using that pivot table I have created a Pivot Chart to show the results in a graphical format, located in worksheet called "Chart".

Ok that’s the data out the way; I hope that everything is clear at this moment.

Firstly I need to work out the ratio between the "New" and "Follow up" data for each month.

Secondly I need to show the ratio on the pivot chart

Hope that was clear, I've attached the workbook.

Thanks in advance

**jevni1974** · 02-01-2008, 11:15 AM

Hiya,

I had a look at your workbook and that looks fine to me.
I am not sure if I understood the word ratio correctly.
What type of ratio do you want to calculate?

**akhlaq768** · 02-03-2008, 09:51 AM

Originally Posted by jevni1974

What type of ratio do you want to calculate?

hi mate,

this is what i want - 1:5 or 1:4..... got something from the net, hope it helps....

Ratios tell how one number is related to another number.

A ratio may be written as A:B or A/B or by the phrase "A to B".

A ratio of 1:5 says that the second number is five times as large as the first.

The following steps will allow determination of a number when one number and the ratio between the numbers is given.

Example: Determine the value of B if A=6 and the ratio of A:B = 2:5

Determine how many times the number A is divisible by the corresponding portion of the ratio. (6/2=3)
Multiply this number by the portion of the ratio representing B (3*5=15)
Therefore if the ratio of A:B is 2:5 and A=6 then B=15

**jevni1974** · 02-04-2008, 07:32 PM

Hiya,

Thanks for posting additional info.

So which type of ratio would you need:

New : Total
Follow up : Total
New : Follow up
Follow up :new

or maybe all of them?

**akhlaq768** · 02-05-2008, 06:09 AM

Hi mate.

I need the ratio for "New : Follow up"

hope to hear from you soon

**jevni1974** · 02-05-2008, 04:03 PM

Hiya,

Well I could not figure it out using a pivot table.
So I created a construction based on an Excel List and the function SUMPRODUCT

This is what I did:
Create an Excel List of the data on the Data sheet
Replace the pivot table with a table that is set-up using the SUMPRODUCT function. It looks the same as the pivottable setup, the only difference is that it can be extended easily with formula based columns.

for more info about excel Lists and the SUMPRODUCT function, have a look at www.cpearson.com

Hope this helps.

**akhlaq768** · 02-12-2008, 06:24 PM

Cheers bud....

you a star...

**akhlaq768** · 02-20-2008, 12:23 PM

Hi jevni1974,

I've just looked at the ratio with the sum product... I'm trying to replicate what you created with the data I have, but unfortunately I can't do it....

Unfortunately, this forum doesn't allow me to attached more than 1Mb,

is it possible i can email you the pivot tables please.

Hope to hear from you soon

many thanks