Grid Search - range data

[Aussie]

Hi everyone.

I've got a grid of data like this (headings are color):

----------0-19 20-30 30-50 50-69
00000-999 9.47 7.12 5.37 4.77
1000-2499 9.07 6.72 4.97 4.37
2500-4999 8.52 6.17 4.42 3.82

The green heading refers to an average sale amount ($).
The Blue heading refers to a monthly sales figure ($)
The data within the cells are a percentage of service fee applicabl
(%) based on the average sale amount and the monthly sales figure.

I would like some help getting excel to search the grid when given a
average sale amount and a monthly sales figure.

IE: $25 avg sale & $1450 monthly sales figure - 6.72% service fe
applicable.
I'm sure that excel can do this - but I've got no idea how! :confused


Can anyone please help me out in a clear manner (assume that I'm a les
than advanced excel user!).

Thanks,
Sarah

 

[Andy Brown]

I would like some help getting excel to search the grid when given an

average sale amount and a monthly sales figure.

1st change your column headings to the lower limits (0, 20, 30, 50),
likewise your row headings (e.g. 0, 1000, 2500).

With monthly fig in G1, the formula

=MATCH($G$1,$A$2:$A$4,1)

will return the position of the cell within A2:A4 (1, 2 or 3) nearest in
value but not greater than monthly fig. This gives you which row to look in.
Similarly for avg sale in G2 (which column to look in),

MATCH($G$2,$B$1:$E$1,1)

This gives you the co-ords for the INDEX function, i.e.

=INDEX($B$2:$E$4,MATCH($G$1,$A$2:$A$4,1),MATCH($G$2,$B$1:$E$1,1))

where B2:E4 = the "matrix" to search.

Rgds,
Andy

 

[Max]

One way ..

Assume you have the service fee % table below in Sheet1
(with col A's and row1's headers amended* as shown)

------......0....20...30....50
0000 9.47 7.12 5.37 4.77
1000 9.07 6.72 4.97 4.37
2500 8.52 6.17 4.42 3.82

*To reflect only the threshold figures

In another sheet, Sheet2
----------------------------
Let's say you have in cols A and B,
data from row2 down, where

Col A = monthly sales figures
Col B = average sale amounts

viz.:

MthlySales.....AvSaleAmt
1450..................25
1200..................35
etc

Put in C2:

=OFFSET(Sheet1!$A$1,MATCH($A2,Sheet1!$A:$A,1)-1,MATCH($B2,Sheet1!$1:$1,1)-1)

Copy C2 down as many rows as you have data in cols A and B

Col C will return the applicable service fee percentages
from the table in Sheet1 for the corresponding figs in cols A and B

For the sample figs above,
C2 returns 6.72 and C3 returns 4.97

 

[Aussie]

Andy / Max,

Wow!

Thanks so much for your prompt assistance!



Sarah

 

[Max]

Pleasure' Sarah !
Thanks for the feedback
(from us)