total newbie seeks advice

truered

Board Regular
Joined
Oct 26, 2002
Messages
58
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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Board Regular
Joined
Oct 26, 2002
Messages
58
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Board Regular
Joined
Oct 26, 2002
Messages
58

ADVERTISEMENT

thanks aladin your a star m8
 

truered

Board Regular
Joined
Oct 26, 2002
Messages
58
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

Well-known Member
Joined
May 20, 2002
Messages
890

ADVERTISEMENT

Expand your list to include your variables.
Book1
ABCD
11001019910
220020
330030
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

Board Regular
Joined
Oct 26, 2002
Messages
58
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
 

Forum statistics

Threads
1,144,217
Messages
5,723,063
Members
422,477
Latest member
pete101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top