if then help needed

[stashman]

Not very good at IF/THen statements, need to do the following:

I want formula to look at cell value with "X" and lookup and do various calculations based on where it falls in following table:
0-2
3-5
6-15
16+

If("cell value"<=2, calc.,) then I am lost.

Can anybody help?? I need to have formula all in one cell.

[Brian from Maui]

Stash,

Have a look at SUMIF

SUMIF(range,criteria,sum_range)

[nicruzer]

On 2002-05-21 09:52, stashman wrote:
Not very good at IF/THen statements, need to do the following:

I want formula to look at cell value with "X" and lookup and do various calculations based on where it falls in following table:
0-2
3-5
6-15
16+

If("cell value"<=2, calc.,) then I am lost.

Can anybody help?? I need to have formula all in one cell.

Try nesting the statements...

If("cell value"<=2,calc.,If(AND("cell value">2,"cell value"<=5),calc.,If(....etc.)))

This could, obviously, get cumbersome, but it works.

[stashman]

Thanks for the previous input....

I got it to work for part of my problem but here is where I am still stuck:

Actual tables to reference are:
0-2.99999
3-5.99999
6-15.99999
16-25.9999
26+


(F3/365) is my cell I am calculating where it falls in the table above. Based on where it falls is a calculation I do. I am running into nesting problems (I think), because if I try to add any additional calcs to the formula below, it bombs. Any ideas??


=IF((F3/365)<3,5,IF(AND((F3/365)>=3,(F3/365)<6),5.67,IF(AND(F3/365)>=6,(F3/365)<16,7.34)))

[maxflia10]

Stashman,

Have you tried using VLOOKUP to get a value from the columns?

[stashman]

Actually, the value in F3 is a calculated field, where I need to know what range it falls into in the table, in order to pull the correct number from there. My understanding is VLookup is great when looking for exact matches, but can it tell me where it falls in a range??

[maxflia10]

VLOOKUP will get a value equal too or less than in the first column. If you have say 1,2,3 in column A and F3 is 2.5 it will display the value associated with 2 in column B

[Aladin Akyurek]

On 2002-05-21 13:37, stashman wrote:
Thanks for the previous input....

I got it to work for part of my problem but here is where I am still stuck:

Actual tables to reference are:
0-2.99999
3-5.99999
6-15.99999
16-25.9999
26+


(F3/365) is my cell I am calculating where it falls in the table above. Based on where it falls is a calculation I do. I am running into nesting problems (I think), because if I try to add any additional calcs to the formula below, it bombs. Any ideas??


=IF((F3/365)<3,5,IF(AND((F3/365)>=3,(F3/365)<6),5.67,IF(AND(F3/365)>=6,(F3/365)<16,7.34)))

It looks like you can modify your table such that it consists of:

{0,5;
3,5.67;
6,7.34;
16,"x";
26,"y"}

where you can fill in what x and y must be. And, you can add more records to this 2-column table.

Supposing that the range of the table above is named Table, you seem to need:

=((F3/365<3)+(F3/365>=3))*VLOOKUP(F3/365,$A$2:$B$6,2)