Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
I can get this to work through brute force, but it isn't elegant.
I have one box into which a number is entered, i then have a list of values.
The number in the box is basically used to do a price calculation.
This is the problem, the number in the box needs to be rounded up to the closest value in the list
example.
it isn't quite so simple as the list doesn't have a pattern but this should explain it.
list is 100, 125, 150, 175, 200, 225
The value entered in the box is 102, the subsequent calculation needs to treat it as 125.
The value in the box is 134, so it is 150.
etc
Is there a simple way of doing this, or is VBA brute force the way forward?
Huh????
wait i get it now after reading it slowly 💡
another cell with =v/hlookup(value,array,2,true) should work right??
vlookup gives the closest value below, or at least it does in my fiddling.
Your "answers" are all multiples of 25
To round up, say your basic price is in cell A1 and you want the answer in B1:
=(INT(A1/25)+1)*25
workings:
INT(102)/25 = 4 {returns the integer of 102/25, i.e. 4 instead of 4.08}
4+1 =5{makes sure it always rounds up}
5*25 = 125 {multiplies back up to the price}
Or am I missing something?
the real data isn't all multiples of 25, i just used that in the list.
It's the lies I can't stand - dammit!
How many prices do you have?
i don't have the list to hand but around 30 values ranging from 100 to 1230, i know i can do an "IF" and get it to work, but the nest limit of 7 means i would have to go VBA, it also seems rather brutal.
The Vlookup looks neater but always returns the item below the one i want..... there must be away of looking at the cell beneath the cell refered to by the vlookup?
Use VLOOKUP(Value,Array,2,True)
and make the array like this:
100 125
125 150
150 175
175 200
200 225
Is that right?
You can do more ifs or rather use two or more of 7 if you name them (insert > name > define) and shift + insert to paste them into the reference bit. Then reference them as =IF(formula_a, formula_a, formula_b)
See: http://www.cpearson.com/excel/nested.htm
But you'll need to do the formulas with if ands: =if(and(A1>0, A1<11),10,if(and(A1>10, A1<21,20, etc.
How about dividing each number in the list by the number in the "box". These will either be less than 1 or greater than one. Then the one you want will be the minimum of the set of those with a ratio > 1.
Thus should be possible with if(), min() and lookup type functions. Will also work even if the list is in a random order rather than ranked.
Edit.
Actually this only needs min and lookup functions and no nasty nested ifs which are a PITA.
[s]mrmo, you can use an array formula like this:
array of your list of values is A1:A10
check number is B1
{=MATCH(B1, A1:A8)}
{} means Array formula - when you've typed in =MATCH(B1, A1:A8), then press ctrl+shift+enter and you get the array formula.
it will return the position of your check number in the array, so to bring back the nearest number use:
{=OFFSET($A$1, MATCH(B1, A1:A8), 0, 1, 1)}[/s]
EDIT, by [s]some fluke you dont actually need it to be an array formula.[/s] as you've already found with the vlookup function,
The Match reference will always bring back the closest lowest result in the array, so then all you need is to use the offset function to make it the next highest number in the array:
=OFFSET($A$1, MATCH(B1, A1:A8), 0, 1, 1)