Log In
Register
Home
Active Member
February 22, 2020
Offline
Hi I need some help with a formula as it has multiple conditions. An example below, I have a Column of percentages (column A) and want the formula in column B to say if Cell A3 is greater than 100 outcome is formula shown. If it’s between 90.1 and 99.9 but end in a 0 then the formula in 2nd row or if ends in any number 1 to 9 then The formula in the 3rd row. Finally if the figure in column A is 90% or less I want it to return 0. I want the results shown in column 2 but one formula that can be pasted down. Hope this makes sense. Can anyone help?
118.0%. . 136. RIGHT(A3,2)*2+100
91.0% 10.00 RIGHT(A4)*10
92.5%. 25 RIGHT(A5,2)
October 5, 2010
Offline
Hi Louise,
It looks like you are working with numbers stored as text. The RIGHT function is for strings. So is everything a string or do you have numbers mixed in?
If it’s between 90.1 and 99.9 but end in a 0 - like 91.0? Or 90.1 which as a number does end in a zero, i.e. 90.10. Again, numbers stored as text?
Please supply your workbook which will make it easier to answer these questions.
Regards
Phil
Active Member
February 22, 2020
Offline
Hi Philp
I am working with numbers i am trying to work out bonus's based upon percentage of individual sales. Tthe final calculation (which works in collumn AB) is based on 60% of group sales and then 40% of individual sales whihch is collumn Y that i am having a problem with. I have attahed the spreadheet so you can see and it.
To explain how the individual bonus works, if they get over 100% the amount over is double e.g 118% becomes 136 however if they get below 100% and between 90.1 and 99.9 they get the last 2 digits x 10 e.g. 95.0% they get 50 or 93.2% they get 32 and anything under 90.1 they get nothing.
Hpe thus explains what i am trying to achieve better and thanks in advance for your response.
Louise
Trusted Members
December 20, 2019
Offline
So you dont really need to know the last charecter of the Percentage, we can just do a calucation on it?
Are you sure that the person getting >90.1 <99.9 gets more of a bonus than the person get over 100? Or have i read that wrong?you can adjust the numbers and calculations if need, but the below should work.
=IF(X2>1,(X2-1)*2,IF(AND(X2>0.901,X2<=0.999),(X2-0.9)*10,IF(X2<=0.901,0,"")))
Purfleet
Active Member
February 22, 2020
Offline
Thank you so much i must have been trying to overcomplicate it! I have had to tweak the calculations to get the results i wanted but it has worked.
=IF(X2>1,(X2-1)*2*100+100,IF(AND(X2>0.901,X2<=0.999),(X2-0.9)*1000,IF(X2<=0.901,0,"")))
Many thanks again!
