Hi There,
In one column I have a range of percentages and I would like to bucket the 20%s as 20%, and if the value is between 30 and 39% bucket it as 30% and so on.
An example of my formula which does not work looks like this.
=IF(AND(V8>=0.2,V8<0.29),"20%",IF(AND(V8>=0.3,V8<0.39),"30%",0))
and my values are in this format 46.4285714285714%.
Any help on this would be greatly appreciated.
Regards
RichG
Why not use ROUNDDOWN?
=ROUNDDOWN(V8,2)
Click on star (*) below if this helps
Why is your formula not working? If the value in V8 is a number formatted as percentage, your formula should work. Is it possible V8 is text?
Another formula that would work
=ROUNDDOWN(V8,1)
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
hi RichG, or maybe:
=FLOOR(V8,0.1)
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
ChemistB:
ROUNDDOWN should have a ,2 not ,1 ie, =ROUNDDOWN(V8,2)
@K m
If the values are formatted as percents and he wants 20, 30, etc then that would be single decimal.
ChemistB: Sorry, you are correct. I should have reread the original post
Hi guys thanks for the tips on the ROUNDOWN...
But know I think the formula is wrong. (HARUMPHHH)
It will only return 40% for percentages that are between 40% and 49%. Any thing above or below this returns a zero.
I have tried this one below but no luck
=IF(AND(V8>=0.2,V8<0.29),"20%",IF(AND(V8>=0.3,V8<0.39),"30%",IF(AND(V8>=0.4,V8<0.49),"40%",0))).
Just to confirm if the value is 23.56% it should return 20%
If the value is 38.25% it should return 30%
It would be great to have an extra set of eyes on this as I have been pondering a solution to this all day.
Regards
RichG
Rounddown does work for any value not just 40%. Don't know what your problem is
=ROUNDDOWN(.2356,1) returns .20 or 20%
=ROUNDDOWN(.3825,1) returns .30 or 30%
Last edited by K m; 01-23-2013 at 11:29 AM.
Hi K m
Does the syntax for the if formula look right to you?
Thanks
RichG
Why use IF formula if ROUNDDOWN works?
Because I like to over complicate things :-P Its my superpower you know.
Sorry about that, it's a bad habit I am trying to get out off.
Thanks for your help and patience. I will sheepishly change the status to SOLVED.
FYI
=IF(AND(V8>=0.2,V8<0.29),"20%",IF(AND(V8>=0.3,V8<0.39),"30%",IF(AND(V8>=0.4,V8<0.49),"40%",0)))
First off, your percents should not be in Quotes. Excel will see them as text. Also, you need your < values to be <=. So
=IF(AND(V8>=0.2,V8<=0.29),20%,IF(AND(V8>=0.3,V8<=0.39),30%,IF(AND(V8>=0.4,V8<=0.49),40%,0)))
Hope that helps.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks