Thread: Percentages

Hey all,

I have got something to work out percentages but want to add something to it and can't work out how.

I have a total number for an item, this number includes add-ons to the item. Another column has the total number 'gone' from the overall total of the item. I now want to separate the AddOns to give a separate percentage.

E.g. Item 1 has a total of 79 and 29 of these are AddOns. 15 are gone and this is from the 79, which gives me 19%

I would like another column that gives another percentage and this would be the 'Total Gone' from the 'Overall Total' less the AddOns

In this example the new percentage (in new column) would be 30% (50/15)

I know I have to subtract the AddOns from the Total but my maths is rubbish and don't know the right way to do it...

Make sense?percentage.xls

hope this helps...

Thanks guys,

The one that works best is yours adaws however when I used your formula it throws up some percentages over 100% which isn't right.

It seems to require an extra calculation to include column D so that if there are Addons and these have all gone as well as the total number of items the new percentage should still be 100%

I've attached a sheet with a lot of entries to show you what happens.

Thank you

percentage-full.xls

Also is there a way of showing the total percentage of both % columns as two separate percentages?

Hi Toxictoad,

The percentage shows up more than 100 is ideally because the numerator is greater than the denominator, if it has to be that ways then a IF condition can be used in the formula and get it back to 100% incase it goes beyond hundred....

In Cell G1 enter this formula =IF((C2/(A2-B2))>1,1,(C2/(A2-B2))) and copy it down... let me know if it works

Thanks for the IF formula guys...didn't realise things were going to get so complicated but that seems to work well

One last thing, I have an overall % that shows the total complete, this is done by first adding all the values from the 'Total' column and then another to add the values of the Total Gone and finally divide these 2 together but how do I do it for the new column 'AddOns Gone'?

I've attached the spreadsheet, the 61.1% is right but the 10.2% isn't because it's not adding\subtracting something, but like I said maths has never been something I've been good at :-/

Thanks

percentage-full.xls

Don't know if this is right but it kind of looks about right, but maybe the figure is to high?

=SUM(D3:D68) - This is the 'Total AddOns' column - figure stored in M3

=SUM(F3:F68) - This is the 'AddOns Gone' column - figure stored in M4

=M3/M4/10 - This is how I got the 'Total %' Ecluding Addons

61.1% is the total gone including Addons and 98.4% is the total gone excluding AddOns

Is this right?

in cell k1 write this formula, =SUM(B3:B68), and then in G1 the formula would be =J1/(I1-K1) this should give the total % excluding add ons

Thank you very much for your time and help