0

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!

Improve this question
2

[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%".

Improve this answer
  • 1
    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
  • 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 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
  • the same percentages will apply, however there are deals from both ANZ & NZ, and for the NZ ones i need to represent the dollar figure as if it was in AUD, hence the 0.8 – Rohan F Mar 20 '13 at 23:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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