IF Command Assistance -

[John Gill]

Hello Team

I wonder if you please help me with a nested "IF" Function formula. I am trying to put indicators as below 10, 20 & 30 in (say column B,) against a set of figures in column "A". EG figures between 10000 & 20000 =10, 20001 to 30000 = 20 & figures between 30001 to 40000 = 40.

Many Thanks

 

[PaddyD]

try:

=FLOOR(A1/1000,10)

Paddy

EDIT - but only if you didn't mean to write 40 as the option for between 30001 - 40000!!
This message was edited by PaddyD on 2002-09-22 16:28

 

[John Gill]

Thank you Paddy, but this does not overcome the problem where 20000 will be in the range to have an indicator of 10 and 30000 will be in the range to have an indicator of 20 and so on.

I thought that the nested "IF" command would overcome this problem, but I will be guided by your expertise.

Many Thanks

 

[PaddyD]

In general, you relate one item to another using one of the lookup functions. (I only went down the floor() route 'cos I din't read your question properly!) This includes number ranges. This approach is preferable to using if's for at least 2 reasons - you aren't restricted by the 7 nested ifs limit; it forces you to keep you comparison bands etc out on the spreadsheet, not hidden away in the formulas (good for future maintenance,auditing etc).

See the simulation:

Book6
	A	B	C	D	E
1	Band	Return		Data	Result
2	0	0		1	0
3	10000	10		11000	10
4	20001	20		20001	20
5	30001	40		30500	40
6				40000	40
7				99999	40
8
Sheet3

Notes:

1) =VLOOKUP(D2,A1:B5,2,1)

The 1 at the end tells the vlookup to search for the closest match, not an exact one.

2) In the bands, put the lowest number in the band in col A...



Does this help?

Paddy
This message was edited by PaddyD on 2002-09-22 16:47

 

[Dave Patton]

On 2002-09-22 16:35, John Gill wrote:
Thank you Paddy, but this does not overcome the problem where 20000 will be in the range to have an indicator of 10 and 30000 will be in the range to have an indicator of 20 and so on.

I thought that the nested "IF" command would overcome this problem, but I will be guided by your expertise.

Many Thanks

try the following Ensure that what you worded is what you require. Test the formula.

=LOOKUP(A2,{0,10000,20001,30001;0,10,20,40})

You can certainly put the Lookup information in a Table on the ss.
This message was edited by Dave Patton on 2002-09-22 16:54

 

[John Gill]

Thanks Paddy.