Excel Spreadsheets

0james0

I'm looking to finish off the spreadsheet I have made for tracking my work. I need to put in what I'm sure is a fairly simple function, but I don't have a clue how!

I get paid 2 different amounts, depending on the source the business came from. I'd like my spreadsheet to know what % I get, depending on what I select from the two choice drop down box. Would make life loads easier for me as I wouldn't need to do so much maths as I could put in the gross amount and then it would take care of the rest.

Anyone have any idea?

Cheers guys

bevanuk

Can you explain what percentage you need?

Obviously you seem to have the drop down function sorted you would need an if formula in the percentage. Say for example the two options in the drop down menu were "Rabbit" and "Cat" - you could set it so your percentage always showed the sum for "Cat" and the sum for "Rabbit" only appeared if "Rabbit" was selected.

With the below I am saying the word value is in D2
=if(D2="rabbit",=sum(your sum for "Rabbit" goes here),=sum(your sum for any other value, in this case "Cat"))

If you can explain the sum and the cell that the value is pulled from I can explain further.

Leopard

What percentage of what?

You need to clarify (greatly) what it is you wish to find out and the actual calculation that you wish Excel to perform.

0james0

Sorry for being so vague!

Helps to give enough info for you to actually answer the question!

If the business is "self generated" I get paid 130% of the annual premium and if it is from "Branch" then I get paid 80% of the annual premium.

There are about four different things I get paid on, but once I have this one, I can use the formula as a template for the others.

0james0

and now I think about it, I don't think I even have my drop down lists made properly! :rolleyes:

I have been just typing the information in the cell, I have managed to create a drop down list in each header, which s useful for sorting the columns in order, but probably won't help here!

Will I need to have drop down choices? Or can I simply have "IF D2 = text then......" ??

Thanks guys, as always you are great.

0james0

Ignore post 5.

I now have drop down menus on my spreadsheet. Managed to remember how I did it a while back and have managed to get them in through luck more than judgement! -but they are there none the less!

Leopard

You still haven't made it clear what it is that you are actually trying to achieve.

What is it you want to calculate as a percentage of what?

Without this information, nobody can help you.

0james0

I'm not really sure how to make it more clear?

For example, if I arrange life cover for a client of mine, the amount I get paid varies depending on the source of the business. I have a spreadsheet I use to keep track of all my cases and the amount they are earning me.

I want to be able to type in a monthly premium and have the sheet calculate how much that will earn me based on the source of that piece of business.

The total amount is always the monthly premium x 12 x either 80% or 130%.

So I want to be able to type in the monthly premium and then it will auto do the sums for me. There is also a drop down box that I can choose the source of business from, there are four dfferent options, with 3 of those having the exact same commission split. I want the information that is selected in the drop down box to be the "IF" factor in the sum.

So if I select "branch" in the drop down list, then I want to be able to punch in the monthly payment and have an auto sum work out what "monthly payment x 12 x 80%" equals.

Or if I select "self" in the list, the sum needs to be "monthly payment x 12 x 130%"

foogirl1983

Some people are quite touchy today!

I'm guessing that you'll be looking at a function of the annual premium. The formula I would say you'd want would be an IF function:

=IF([dropdowncell]="Branch",(premium*1.3),(premium*.85))

Dave101t

wouldnt access be better than excel for this?

0james0

foogirl1983 wrote: »

Some people are quite touchy today!

I'm guessing that you'll be looking at a function of the annual premium. The formula I would say you'd want would be an IF function:

=IF([dropdowncell]="Branch",(premium*1.3),(premium*.85))

Spot the error! This is what I have put in the cell I wanted to do the calculations:

=IF(F4="Self",K4*1.3,K4*0.8)

It returns a 0 no matter what is selected in the drop down box and put in the premium cell.

F4= the drop down cell, "Self" is something I have introduced myself (giving the 1.3 times annual premium). There are 4 options in the dropdown box in total, but they

K4= the cell where the annual premium would go.


Not sure what I'm doing wrong, but then that is probably because I don't know what I am doing!