40 IIf statements efficiently??

abrown

Board Regular
Joined
Nov 15, 2003
Messages
172
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
Joined
Sep 3, 2002
Messages
675
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
Joined
Aug 5, 2003
Messages
12,251
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
Joined
Aug 5, 2003
Messages
12,251
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
Joined
Sep 3, 2002
Messages
675

ADVERTISEMENT

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

abrown

Board Regular
Joined
Nov 15, 2003
Messages
172

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
Joined
Jan 19, 2004
Messages
36
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
Joined
Jan 19, 2004
Messages
36
OK: In a new field- Retail_Range:Partition([Retail Price],0,400,5).

This should work.
 

abrown

Board Regular
Joined
Nov 15, 2003
Messages
172
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
 
Master Excel Bundle

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.

Forum statistics

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

Which adblocker are you using?

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
Top