Caculating multiple percents in a worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paula - 16 Jan 2008 04:44 GMT

Hello,
Using Excel 2003, what is the formula I would use to accomodate the
automatic calculation of multiple percents within the same worksheet?

For example, if I needed to know what both 6% and also 9% as below?

Subtotal % of Subtotal Total added to %
of Subtotal
10 + 25 = 35 6% 37.1
15 + 26 = 41 15% 47.15

Thanks,
Paula

Reply to this Message

ShaneDevenshire - 16 Jan 2008 06:30 GMT

Hi Paula,

We're going to need more info. Where is 9%? What does 35 6% 37.1 mean?
Are they in different cells? How does 6% of 35 equal 37.1? Where did 15%
come from?

Signature

Thanks,
Shane Devenshire

> Hello,
> Using Excel 2003, what is the formula I would use to accomodate the
[quoted text clipped - 10 lines]
> Paula
>

Reply to this Message

Paula - 16 Jan 2008 08:01 GMT

Helo Shane,

I think I am trying to figure out how to write a conditional statement?

For example, for the name of each specific company I type in a cell, I want
their assigned % rate to automatically appear.

For example, if "Smith" then 10% if "Brown" then 15% - so the % rate for
each company will automatically fill in when I type the company's name.

How do I associate a specific % value with each different company, so I
don't have have to type it in for each one manually?

Thanks
Paula

> Hi Paula,
>
[quoted text clipped - 16 lines]
> > Paula
> >

Reply to this Message

Max - 16 Jan 2008 08:14 GMT

Based on your clarification, a vlookup comes to mind ..

Assuming in B2 down would be: Smith, Brown, etc
you could use something like this in say, C2:
=IF(B2="","",VLOOKUP(B2,{"Smith",0.1;"Brown",0.15},2,0))
with C2 formatted as percentage. Copy C2 down

Alternatively, simpler, assuming placed in say, Sheet1's C2:
=IF(B2="","",VLOOKUP(B2,Sheet2!A:B,2,0))
where Sheet2's cols A & B houses your vlookup reference table,
names in col A, percentages in col B

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> I think I am trying to figure out how to write a conditional statement?
>
[quoted text clipped - 9 lines]
> Thanks
> Paula

Reply to this Message

Max - 16 Jan 2008 06:47 GMT

Perhaps using a one variable data table would easily cover your intents to
calculate multiple outputs for the range of multiple input percentages ?

Try this sample whcih illustrates:
http://www.freefilehosting.net/download/3ae0b
Using a one var data table for multiple inputs-outputs.xls

Assume the single variable is the percentage input in D2,
and the output is calculated in E2,
where E2 in this instance, contains the formula: =C2*(1+D2)
[C2 is assumed a constant here]

Here's a quick way to set-up a one variable data table
in an adjacent area on the same sheet

Put in H2: =E2 (just a simple link to the output cell in E2)
List the range of possible percentages in say, G3:G18
eg input in G3:G18 : 5%, 6%, ... 20%

Select G2:H18, then click Data > Table
Leave row input cell blank
Enter in column input cell: D2
Click OK. That's it.

H3:H18 will return the results corresponding to the percentages in G3:G18.
You can change the input percentages in G3:G18 if you so wish, H3:H18 will
recalc accordingly.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Hello,
> Using Excel 2003, what is the formula I would use to accomodate the
[quoted text clipped - 10 lines]
> Paula
>

Reply to this Message