Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.