multiple IF

[pytelium]

I have a spreadsheet as follows

Col B can be either F or N

Col J Numbers From 5 TO 36
Col W Numbers from 1 to 100

I want to create a new column based on some complicated frormulae.

If col b is F and col j is less than 5.9, the formula is
2.193*((.99^w1)*(w1^.584))
where w1 is the number in that cell.

If col b is F and col j is between 5.9 and 6.9 the formula is
2.424*((.99^w1)*(w1^.668)) where w1 is the number in that cell

If col b is F and col j is between 6.9 and 7.5, the formula is
2.79*((.99^w1)*(w1^.612))where w1 is the number in that cell

If col b is F and col j is between 7.5 and 8.5, the formula is
3.052*((.99^w1)*(w1^.654))where w1 is the number in that cell

If col b is F and col j is between 8.5 and 11.9, the formula is
3.445*((.99^w1)*(w1^.665)where w1 is the number in that cell

If col b is F and col j is between 11.9 and 12.5 the formula is
4.942*((1.004^w1)*(w1^.494))where w1 is the number in that cell

If col b is F and col j is between12.5 and 14.5 , the formula is
3.641*((.99^w1)*(w1^.772))where w1 is the number in that cell

If col b is F and col j is greater than 14.5 , the formula is
3.853*((.984^w1)*(w1^.856))where w1 is the number in that cell

If col b is N and col j is ANY VALUE , the formula is
8.017*((.9832^w1)*(w1^.7566))where w1 is the number in that cell


i presume to do this I will need IF and AND functions,but I cant get to
do it.

 

[adsfjkjafsd]

I would build a lookup table to determine the values that vary based on the
value in col J when the value in col B is "F" and then use a single If
statement as follows:

If (B1="N",
8.017*((.9832^w1)*(w1^.7566)),Lookup(J1,LookupTable,2)*(((J1,LookupTable,3)^
w1)*(w1^Lookup(J1,LookupTable,4)))

Steve

 

[pytelium]

Thanks Steve,unf I have never used this LOOKUP function before,I tried
the help in Excel. I am getting a syntax error. I am not sure what to
do,do I type a table of values somewhere else in the sheet,LOOKUP is in
the help but not LOOKUPTABLE.do I type in LOOKUPTABLE or the cell where
the table is?

 

[adsfjkjafsd]

Pytelium,

My apologies, I was working from a dusty memory. You will want to refer to
help for the Vlookup functions, and yes, the LookupTable is a range of
values that you will enter somewhere else in the sheet or book.

Your lookup range will look like the following.

5 2.193 0.99 0.584
5.9 2.424 0.99 0.668
6.9 2.79 0.99 0.612
7.5 3.052 0.99 0.654
8.5 3.445 0.99 0.665
11.9 4.942 1.004 0.494
12.5 3.641 0.99 0.772
14.5 3.853 0.984 0.856

After entering the range of values I will typically name that range
"LookupTable" so that I can then refer to it by name.

The formula I gave you earlier should now look like the following:

If
(B1="N",8.017*((.9832^w1)*(w1^.7566)),VLookup(J1,LookupTable,2,True)*((VLook
up(J1,LookupTable,3,True)^w1)*(w1^VLookup(J1,LookupTable,4,True)))

Because of the true statement, the VLookup function will read from the row
in the LookupTable where the first column is less than or equal to the value
in Col J. The value following LookupTable in the formula indicates which
column contains the values you want. In this instance if the value in
column J is less than 5 you will get an error message.

Steve

P.S. How do I get my response to post to the newsgroup? My first response
to you does not appear.

 

[pytelium]

Thanks Steve, I am afraid still getting error,away for a few days,wil
try later in week

 

[BenjieLop]

I will just elaborate on what Steve has started. You can set up your
table as follows:

For example, in Cells AB1 to AB8, enter the following numbers:

5
5.9
6.9
7.5
8.5
11.9
12.5
14.5

In Cells AC1 to AC8, enter the following numbers

2.193
2.424
2.790
3.052
3.445
4.942
3.641
3.854

In Cells AD1 to AD8, enter the following numbers

0.99
0.99
0.99
0.99
0.99
1.004
0.99
0.99

and, in cells AE1:AE8, enter the following numbers

0.584
0.668
0.612
0.654
0.665
0.494
0.772
0.856

The formula to use is as follows:

=IF(B1=\"N\",8.017*((0.9832^W1)*(W1^0.7566)),VLOOKUP(J1,$AB$1:$AE$8,2)*(VLOOKUP(J1,$AB$1:$AE$8,3)^W1)*(W1^VLOOKUP(J1,$AB$1:$AE$8,4)))

I hope this will help you.

Regards.

 

[smcferrin]

Pytellium,

It might help if I could see the formula you are using. Can you copy
and paste your formula into a reply?

Thanks,
Steve

 

[pytelium]

Steve and BenjiLop,thank you both,BenjiLop's solution works for me now.

 

[BenjieLop]

Thank you for the feedback.

Regards