total newbie seeks advice

[truered]

Hi guys,I am very slowly getting to grips with excel,and I am totally stuck on a project i am doing.I have a series of 6 cells of numbers, that are calculated to show the average total in a 7th cell,I now want to take the figure from the 7th cell,and to automatically assign a value in an 8th cell,which corresponds to a range of differing values.ie/ if cell 7 =anywhere between 1 and 10 then cell 8 automaticlly assigns x.if cell 7 =anywhere between 11 and 20 then cell 8 automaticlly assigns x.etc
x being = to a range of points,would appreciate any help on layout and formula.
thanks in advance.

 

[Aladin Akyurek]

On 2002-10-27 08:14, truered wrote:
Hi guys,I am very slowly getting to grips with excel,and I am totally stuck on a project i am doing.I have a series of 6 cells of numbers, that are calculated to show the average total in a 7th cell,I now want to take the figure from the 7th cell,and to automatically assign a value in an 8th cell,which corresponds to a range of differing values.ie/ if cell 7 =anywhere between 1 and 10 then cell 8 automaticlly assigns x.if cell 7 =anywhere between 11 and 20 then cell 8 automaticlly assigns x.etc
x being = to a range of points,would appreciate any help on layout and formula.
thanks in advance.

Did you know that there are 256 7th and 8th cells in a worksheet...

What is a range of points when you say: "x being = to a range of points"?
This message was edited by aladin akyurek on 2002-10-27 10:20

 

[truered]

ok maybe I didn't explain it correctly,as I said totally new to this.it can be any set of cells for this example I'll use a1-a6 therefore a7 is the average of a1:a6,now dependent on the value of a7 a8 becomes a figure based on the following
a7 10 then a8 2
a7 15 then a8 4
a7 20 then a8 6
etc etc
hope this clarifys this

 

[Aladin Akyurek]

On 2002-10-27 10:12, truered wrote:
ok maybe I didn't explain it correctly,as I said totally new to this.it can be any set of cells for this example I'll use a1-a6 therefore a7 is the average of a1:a6,now dependent on the value of a7 a8 becomes a figure based on the following
a7 10 then a8 2
a7 15 then a8 4
a7 20 then a8 6
etc etc
hope this clarifys this

Create a 2-column list like the following...

{1,1;
11,2;
21,3}

The logic of the example list is...

For all a, when a >= 1 and a< 11, b = 1;
For all a, when a >= 11 and a< 21, b = 2;

etc.

After creating such a list, select all of its cells, go the Name Box on the Formula Bar, type LIST, and hit enter.

Now you use in A8 the formula...

=VLOOKUP(A7,LIST,2)

to get the desired assignments.
This message was edited by Aladin Akyurek on 2002-10-27 10:33

 

[truered]

thanks aladin your a star m8

 

[truered]

that worked superbly thanks again,and if I can impose a little bit more of your knowledge,how do I add a variable list to the formula.
example
list
100 to 200 then 10
200 to 300 then 20
300 to 400 then 20
etc etc

 

[maxflia10]

Expand your list to include your variables.

Book1
	A	B	C	D
1	100	10	199	10
2	200	20
3	300	30
Sheet1

Your list in column A must be sorted. From the help files on vlookup

Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

 

[truered]

sorry guys still not figured it,
£100,001.00 1 £500,000.00
£50,001.00 2 £100,000.00
£40,001.00 3 £50,000.00
£30,001.00 4 £40,000.00
£20,001.00 5 £30,000.00
£15,001.00 6 £20,000.00
£12,501.00 7 £15,000.00
£10,001.00 8 £12,500.00
£9,001.00 9 £10,000.00
£8,001.00 10 £9,000.00
£7,001.00 11 £8,000.00
£6,001.00 12 £7,000.00
£5,001.00 13 £6,000.00
£4,001.00 14 £5,000.00
£3,001.00 15 £4,000.00
£2,001.00 16 £3,000.00
£1,001.00 17 £2,000.00
£0.00 18 £1,000.00

L J k

b12 is the cell I want to put the value in,c12 is the cell I want the points total
L,J,K are the cell refs for my table
my formula is B12==VLOOKUP(B12,I1:K18,J1,2)

Where am I going wrong Please

 

[maxflia10]

Enter the formula in C12