Multiple nested if statements

[Amanda]

Hi,

I have a list of 10 possible categories of debt, i.e:

A 0 - 100
B 101 - 200
C 201 - 300
D 301 - 400
E 401 - 500 etc.

I also have a list of debts, by invoice, with varying amounts, i.e:

Invoice 1 $159
Invoice 2 $345
Invoice 3 $677

Against the invoice I want to add the category it falls into, i.e A,B,C etc.
Because there are 10 categories there are too many to use =if(.... how can I
do this?

Thank you

Amanda

 

[Sandy Mann]

With an invoice in C5 try:
=CHAR(64+FLOOR(C5,100)/100)


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

No make that:

=CHAR(65+FLOOR(C5,100)/100)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Amanda]

Hi,

Beyond $600 the bandings widen, so

Category E = 500 - 600
Category F = 600 - 1,000
Category G = 1,000 - 15,000

and so on.

Can it cope with this scenario?

Thanks very much

Amanda

 

[Sandy Mann]

Amanda,

In that case try:

=LOOKUP(C5,{100,200,300,400,500,600,1000,1500,2000,2500},{"A","B","C","D","E","F","G","H","I","J"})


you could also put the 100 - 2500 and A - J in cells and reference them
like:

=LOOKUP(C5,I1:I10,J1:J10)
notice that the letters do not need quotes around them when in cells.

The advantage of the second method is that it is easier to chnge the ranges
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk