Modify array and include 3 conditions in formula

[eblack93]

Good day,

Some time ago, I was helped through this forum with creating a formula for my boss' product prices but now he wants it to be modified. The current formula is this:
"=FLOOR(J8,10)+IF(J8<=99.99,LOOKUP(MOD(J8,10),{0,1.49,3.99,6.99},{0.97,3.97,5.97,9.97}),LOOKUP(MOD(J8,10),{0,1.49,4.99,9},{0,3,9,10}))" but it is only for 2 conditions (prices from $99.99 and below and prices from $100 and above), the arrays are based on the conditions from then and are different to the ones I need to get to.

I attempted to just modify the arrays but I'm getting N/A as the result in some cells but maybe the formula needs to be rebuilt from scratch(?) and my knowledge only goes so far.

Anyways, the conditions I need to match are the following:

-For prices below $99.99:
Round the last integer before the decimal point to x5.97 or x6.97
Any price that ends in 6, 7, or 8 round them down to x6.97; any price ending on 9, 0, 1, 2, 3, 4, or 5 round them up to x5.97

For example:
27.83, 66.00, 18.20 would now be 26.97, 66.97, 16.97
39.37, 91.00, 63.20 would now be 45.97, 95.97, 65.97

-For prices $100 - $500:
All prices will have the last integer rounded to xx5.00 or xx7.00
No specific array was given for this so the same as above would be okay

-For prices above $500:
All prices ending on xx4, xx5, xx6, and xx7 round them to xx7.00
All prices ending on xx0, xx1, xx2, round them down to xx9.00
All prices ending on xx8 and xx9 round up to xx9.00

For example:
324.67, 655.98 would now be 327.00, 657.00
2001.98, 992.35 would now be 1999.00, 989.00
3998.78, 299.00 would now be 3999.00, 299.00

Hopefully, this makes sense. Any help is appreciated, thank you.

 

[Eric W]

Give this a try:

Book2
	A	B
1	Price	Rounded
2	$ 27.83	$ 26.97
3	$ 66.00	$ 66.97
4	$ 18.20	$ 16.97
5	$ 39.37	$ 45.97
6	$ 91.00	$ 95.97
7	$ 63.20	$ 65.97
8	$ 123.45	$ 125.00
9	$ 126.77	$ 125.00
10	$ 129.23	$ 127.00
11	$ 624.67	$ 627.00
12	$ 655.98	$ 657.00
13	$ 2,001.98	$ 1,999.00
14	$ 992.35	$ 989.00
15	$ 3,998.78	$ 3,999.00
16	$ 1,299.00	$ 1,299.00
Sheet14

Cell Formulas
Range	Formula
B2:B16	B2	=FLOOR(A2,10)+CHOOSE(MATCH(A2,{0,100,500}),LOOKUP(MOD(INT(A2),10),{0,6,9},{5.97,6.97,15.97}),IF(MOD(INT(A2),10)<7,5,7),LOOKUP(MOD(INT(A2),10),{0,4,8},{-1,7,9}))

There were a few discrepancies in your description. In the "Above $500" category, you didn't say what to do with xx3, and a couple of your examples were amounts below $500. But see how close this formula is.

 

[eblack93]

My apologies, you are correct; my mind is beyond melting lol. For the xx3 in prices above $500 it is in the round down group along with 0, 1, and 2.

I tested the formula and dear lord, I wish I could arrange a formula like this one day by myself.

One last request if you have time and if it's no issue. Do you mind walking me through the formula so that I can understand it better?

Thank you in advance!

 

[Eric W]

Sure, here's the formula:

=FLOOR(A2,10)+CHOOSE(MATCH(A2,{0,100,500}),LOOKUP(MOD(INT(A2),10),{0,6,9},{5.97,6.97,15.97}),IF(MOD(INT(A2),10)<7,5,7),LOOKUP(MOD(INT(A2),10),{0,4,8},{-1,7,9}))

The FLOOR rounds down the number to the nearest multiple of 10, so 27.83 turns to 20.00, 3998.78 turns to 3990.00, etc.

=FLOOR(A2,10)+CHOOSE(MATCH(A2,{0,100,500}),LOOKUP(MOD(INT(A2),10),{0,6,9},{5.97,6.97,15.97}),IF(MOD(INT(A2),10)<7,5,7),LOOKUP(MOD(INT(A2),10),{0,4,8},{-1,7,9}))

The MATCH function looks at the value in A2, and returns the position where A2 falls in the {0,100,500} array. So 0-99.99 is position 1, 100-499.99 is position 2, and 500-up is position 3. The CHOOSE function takes that value and returns the value in the rest of the function. Since we need 3 formulas, one for each range, this lets us figure out which one to use. They are highlighted in purple, green, and orange.

=FLOOR(A2,10)+CHOOSE(MATCH(A2,{0,100,500}),LOOKUP(MOD(INT(A2),10),{0,6,9},{5.97,6.97,15.97}),IF(MOD(INT(A2),10)<7,5,7),LOOKUP(MOD(INT(A2),10),{0,4,8},{-1,7,9}))

INT(A2) returns the integer part of A2, so it takes off the cents value. Then MOD give us the remainder when we divide by 10. So 27.83 turns to 27.00 after INT, and if you divide 27 by 10, you get the remainder of 7. So that tells us the digit to look at.

The 0-99.99 formula is:

LOOKUP(MOD(INT(A2),10),{0,6,9},{5.97,6.97,15.97})

So we're looking up the last digit (red) in the {0,6,9} array. So anything in the 0-5 range returns the value in the same position in the {5.97,6.97,15.97} array, or 5.97. Likewise 6-8 returns 6.97, and 9 returns 15.97. So when we add that value to the FLOOR value we started with, we get the amount you're looking for.

The 100-499.99 formula is a bit simpler, since you wanted all of those to round to 5.00 or 7.00, I just used an IF to see if the last digit is under 7, if so I use 5, if it's 7 or over, I use 7.

The 500-up formula works just like the 0-99.99 formula, just with other values.


Hope this helps!

 

[eblack93]

Brilliant!

 

[eblack93]

I am unsure if I need to post a new thread for this follow-up but I need some assistance with tweaking the formula even further and make a second version for a different column.
The new conditions I need to match for one formula are the following:

For prices $0 - $10:
Round up only the decimals to .97

For prices $10.01 - $99.99:
Round up to x3.97, x5.97, x6.97 or x9.97

For prices $100 - $500: (this remains the same)
All prices will have the last integer rounded to xx5.00 or xx7.00
No specific array was given for this so the same as above would be okay

For prices above $500:
Round up last integer to xx7 or xx9 and decimals to .00


Now, for the second formula:

For prices $0 - $499:
Round up only the decimals to .99

For prices above $500:
Round up only decimals to .00


Hopefully, this makes sense. Any help is appreciated, thank you.

 

[Eric W]

I'm not sure if you got an answer to this yet. You probably would have gotten a quicker answer if you'd opened a new thread. Try these:

Formula 1:

=CHOOSE(MATCH(A1,{0,10,100,500}),CEILING(A1+0.03,1)-0.03,FLOOR(A1,10)+LOOKUP(MOD(A1,10),{0,3.98,5.98,6.98},{3.97,5.97,6.97,9.97}),FLOOR(A1,10)+LOOKUP(MOD(A1,10),{0,5.01,7.01},{5,7,15}),FLOOR(A1,10)+LOOKUP(MOD(A1,10),{0,7.01,9.01},{7,9,17}))

It's fairly long due to the 4 sub-formulas in it.


Formula 2:

=CEILING(A1,1)-(A1<500)*0.01

Much easier.