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)
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
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
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
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!
