Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a complex formula i need to create to look up various fields, with percentages of totals to be given if certain criteria are met, and need some guidance.

In column P i have a monetary figure. In column D i have a business deal type, New Business, Renewal or Upsell. In column T i have some Inside Sales users (some are blank depending if they assisted)

If a deal is New Business or Upsell I need to work out the % of the monetary figure. (renewals excluded).

If the deal has an Inside Sale usernames i need to calculate 3% of the monetary figure), however if the deal doesn't have an inside Sales user assigned i need to calculate 2% of the monetary figure.

If anyone can assist i would be very grateful!

share|improve this question
up vote 2 down vote accepted

[You should be able to use ISBLANK here too.] Hopefully this is what you want:

SU565092 example

The logic is "Where Renewal, show 0, otherwise if T is blank apply 2%, else apply 3%".

share|improve this answer
pnuts, I think you need to put in a condition to exclude Renewals from the % of monetary amount calculation. – chuff Mar 13 '13 at 13:20
@chuff Spot on, thank you. – pnuts Mar 13 '13 at 13:35
thanks guys much appreciated as always. You mentioned a condition to exclude renewals from the monetary amount, however i think the D2=Renewal,0 formula has catered for this. I have gone through the sheet and can see all renewals have returned a $0 – Rohan F Mar 13 '13 at 22:07
I edited my first answer to take into account what @chuff spotted (you can see I made a right royal mess of things if you click on the time next to "edited" in my answer). Still, got there in the end! – pnuts Mar 13 '13 at 22:19
I have used the below formula to calculate to commission percentage of a certain deal =IF($D97="Renewal",0,IF(ISBLANK($T97),$P97*0.026,$P97*0.031) what i now need to is look up a certain entry in Column C, such as NZ, and if so need to add an exchange rate of 0.8, any ideas how to automate this as opposed to just adding *0.8 at the end of the formual manually? – Rohan F Mar 17 '13 at 22:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .