If Function

[etsusurveyor]

i do not know how to put more than 8 tests in one cell
i need to put 9 with 1 as the false result
ive also tried with all the parenthesis at the end.

Please Login or Register  to view this content.

[jasoncw]

See if this formula will work for you:

Please Login or Register  to view this content.

HTH

Jason

[daddylonglegs]

Perhaps just

=IF(ISNA(MATCH(Z3,W2:W10,0)),1, VLOOKUP(Z3,W2:X10,2,0))

[etsusurveyor]

what if we take away the need for output of 1 for a false statement and have
what i have are a list of 10 names in column "W", list of 10 to 1 in column "X".
i want to display the number in column "X" if name in column "Z" matches name left of number.

Please Login or Register  to view this content.

[timrmason]

I have a similar problem
I am looking for a solution to this spread sheet. A little back ground mite help to under stand what I am trying to do.
I supply and install Accordion shutters, the shutter widths only come in certain sizes. (see ShutSizeChart.pdf) and the building openings varies. When I order, I need to go to the next size larger than the window. The problem is the nesting limit; the size chart contains 70 sizes.
See ShutWorkSh.pdf

[jasoncw]

Try this one instead:

Please Login or Register  to view this content.

Jason

what if we take away the need for output of 1 for a false statement and have
what i have are a list of 10 names in column "W", list of 10 to 1 in column "X".
i want to display the number in column "X" if name in column "Z" matches name left of number.

[etsusurveyor]

it outputs 1 when this particular value is 6
attached is the actual page. if Name1=Name1 then 10. but i need it to run through all of the names incase the name in Z2 is Name 10, then output 1

[jasoncw]

Print screens or the actual Excel file always help. You were using Z3 in your original formula, so that is what we assumed you needed. Instead, try using Z2:

Please Login or Register  to view this content.

Jason

[Koneks]

Try this one

if(iserror(vlookup(Z3,W2:X13,2,FALSE)),1,Z3)

I expect it works because I use Spanish Excel and I had to look for the English formulas.

[timrmason]

I can not seem to get it to work.
What I am trying to accomplish is
I input the actual opening width in C5 and Excel finds the width size that is the same or the next size lager.
Example
The width is 20, which is between 17.6 &23.5 on the Shutter size chart, I enter that in C5 excel returns the value of 30.25 from line 4 on the Shutter size chart.
So I think should be looking for a equal to or greater than type formula but there are so many arguments

[etsusurveyor]

Please Login or Register  to view this content.

this worked for me.... thanks.

so is it impossible to have more than 8 statements in an if function?

[jasoncw]

Yes, there is a max number of nested IF statements. However, you can work around this by doing something like this:

Please Login or Register  to view this content.

HTH

Jason

[timrmason]

It still is not working. I am using whole numbers and desamuls =IF(C12=1,0)+IF(C12=6,6)+IF(C12=11.75,11.75)+IF(C12=23.25,23.25)+IF(C12=29,29)+IF(C12=35,35)+IF(C12=40.75,40.75)
this returns a 0 value.
I have tryed adding a less than
=IF(C11<=1,0)+IF(C11<=6,6)+IF(C11<=11.75,11.75)+IF(C11<=23.25,23.25)+IF(C11<=29,29)+IF(C11<=35,35)+IF(C11<=40.75,40.75)
this returns a 145.75 value witch i think is the sum of the brackets.
any other ideas

[jasoncw]

Timrmason, the replies in this thread were intended for etsusurveyor. As it states in the forum rules, you must start your own thread if you have a question. Please begin a new thread.

Jason