40 IIf statements efficiently??

abrown

Board Regular
I have a table with item # and retail price. The retail prices range from \$0.00 to \$400.00

I would like to create a third column called Retail range. Retail range is in increments of \$5. For example:

0-4.99
5-9.99
10-14.99
15-19.99
etc all the way to 400

Can someone suggest the best way to accomplish this task??? IIf's, queries, combo boxes???

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

ChrisUK

Well-known Member
Try a select case statement if this is indeed what you realy want to do.

It is inefficient to store data more than one in a DB . If you have the cost you don't need to store a range separately in a column of the same table all you ned to do is use a range in the query which you could construct using the select case statement

HTH

SydneyGeek

MrExcel MVP
Which version of Access do you use? In XP and higher (not sure about 2000), you can use a Pivot Table view to group the items by increments of (or whatever interval you like).

SydneyGeek

MrExcel MVP
You could enter this expression in a query:

Price Band: (IIf(Round([Retail Price]/5,1)-Int([Retail Price]/5)<0.5,Round([Retail Price]/5,0)+1,Round([Retail Price]/5,0)))*5

This will give the upper limit of each price band. To group on some other interval, change the 5's to your desired interval.

Denis

ChrisUK

Well-known Member

ADVERTISEMENT

Thats a really nice feature, i didn't realise you could do it. Thanks

abrown

Board Regular

ADVERTISEMENT

Thanks for all of the advice. We tried the IIF in the query that gives you an upper limit and it worked great. BUT the Price column contains some text values so there are #Error messages in the column.

Also what we are really looking for the formula to give us a range and not just the upper limit. So if the price is 14.00 it would give us "10-15" in the price range column.
Is there any way to do this in one formula?

PaulF

New Member
Did you check out the partition function? Use the link above. It is easy and will, I believe, do exactly as you want.

If this gives you a problem post back.

PaulF

New Member
OK: In a new field- Retail_Range:Partition([Retail Price],0,400,5).

This should work.

abrown

Board Regular
The partition formula worked great. Thanks.

The other challenge is that when the price is 5.99 the formula returns the range 6:10. I want 5.99 to fall into 5:9.99 instead.

How can I make the range recognize 2 decimal points? I would like the ranges to layout as follows:

0-4.99
5-9.99
10-14.99
15-19.99
etc all the way to 400

Similar threads

Replies
4
Views
311
Replies
13
Views
502
Replies
2
Views
192
Replies
7
Views
2K
Replies
3
Views
322

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Threads
1,151,895
Messages
5,766,978
Members
425,392
Latest member
Booknerd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back