match up figures

[oldham94l]

I have a table which shows sales and staff:

50 - 2
100 - 2
200 - 3
300 - 5
etc

if i enter my actual sales in A coloum eg.

50
78
220
157
100
35


it will match staff to the figures:

50 - 2
78 - 2
220 - 3
157 - 3
100 - 2
35 -2

the sales alos have to round up or down so 157 would select 200 from the table.

thanks

 

[bolo]

Try this

Book1
	G	H	I	J	K	L	M	N
9
10		50	2			0	2
11		78	2			200	3
12		220	3			300	5
13		157	3
14		100	2
15		38	2
16
Sheet1

 

[oldham94l]

no, it says #NAME? instead of the number

 

[Andrew Poulsom]

You need to install the Analysis Toolpak to use MROUND (Tools Add-Ins).

 

[oldham94l]

i can setup the addin, but i have to send the final workbook to 32 stores who will not be able to install addins,

is there a way to do it without MROUND?

 

[attc]

MROUND(H10, 100) is equivalent to:
ROUND(H10,-2)

{Also, more generally, MROUND(x, y) = ROUND(x/y,0)*y}