Nest more then seven if conditions

[LadyHawk]

I want to nest more than seven "if" formulas, because i'm comparing values of
twelve months.
Therefor, because excel has a limit of nesting seven if conditions, I want
to know if there is an operator or a formula that can combines/concatenates
two columns with, for example, half of the if conditions I need.
I really apreciate your help with this.

 

[Ron Coderre]

There's more than one way to handle your issue.

Let us know what kind of data you're dealing with, what you want to do, and
what formula you've tried. That way you'll get a response that's more
tailored to your situation.

***********
Regards,
Ron

XL2002, WinXP

 

[Joel]

Yes if can be nested in multiple cells. An if returns a default value ot
eitther trye or false
example

in cell A1

=if(C6 = 7,,) this will return true if C6 = 7 and false otherwise.

in cell A2
=if(C6 = 8,,) this will return true if C6 = 8 and false otherwise.

then you can say in A3
=if(A1 and A2,"7 or 8") this will return true if C6 = 7 or 8 and false
otherwise
You could of had in A3
=if(C6 = 7,if(C6 = 8),"7 or 8")

 

[Niek Otten]

http://www.j-walk.com/ss/excel/usertips/tip080.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I want to nest more than seven "if" formulas, because i'm comparing values of
| twelve months.
| Therefor, because excel has a limit of nesting seven if conditions, I want
| to know if there is an operator or a formula that can combines/concatenates
| two columns with, for example, half of the if conditions I need.
| I really apreciate your help with this.
| --
| LadyHawk

 

[Don Guillett]

Hard to tell without seeing specifics but usually a VLOOKUP table will do.
See the help index.

 

[ΓΙΑÎÎΗΣ Χ.Î’.]

In a new clear sheet write (name of sheet: test)

cell A1 : =IF(logical_test1,value1_if_true,A2)
cell A2 : =IF(logical_test2,value2_if_true,A3)
cell A3 : =IF(logical_test3,value3_if_true,A4)
cell A4 : =IF(logical_test4,value4_if_true,A5)
...........................................................
cell A12 : =IF(logical_test12,value12_if_true,"out of choices")


Now use in any cell of book the function
=test!A1

The sheet "test" can be hidden

The same with define name

nameA=IF(logical_test1,value1_if_true,nameB)
nameB=IF(logical_test2,value2_if_true,nameC)
nameC=IF(logical_test3,value3_if_true,nameD)
......................................................
nameL=IF(logical_test12,value12_if_true,"out of choices")

Now use in any cell of book the function
=nameA

Ioannis Varlamis, Athens Greece

 

[LadyHawk]

Sorry I'd never answer you back, but I never came back to this site again.
But I still have the same problem.
Here's the situation:
I have to calculate the variation of the price cost of several products,
within a data range, that goes from July/06 until today (May/07), considering
that the first month is the reference value.
It gets more complicated because there are products that where bought, for
example, in September/06. So, the price cost of this month as to be reference
value.
I forgot to mention that I also have a column that has the total amount of
product that have been bought throughout the data range.
Below this, I demonstrate the calculations that I'm doing until February/07):
IF(BZ4=0;0;
if the price of the present month is equal to zero, then return zero. If
not, do:
IF($I4<>0;(BZ4/$I4);
if the price of the reference month is different from zero, then divide the
price of the present month (February/07) with the price of the reference
month (July/06). If not, do:
IF($X4<>0;(BZ4/$X4);
if the price of the following month (August/06) is different from zero, then
divide the price of the present month (February/07) with the price of that
following month. If not, do:
IF($AG4<>0;(BZ4/$AG4);
this does the same evaluation indicated above;
IF($AP4<>0;(BZ4/$AP4);
this does the same evaluation indicated above;
IF($AY4<>0;(BZ4/$AY4);
this does the same evaluation indicated above;
IF($BH4<>0;(BZ4/$BH4);
this does the same evaluation indicated above;
IF($BQ4<>0;(BZ4/$BQ4);1))))))))
this does the same evaluation indicated above, but if false, it means that
it's the first time that this product appears, so it places the number 1 (or
100%).
I've tried different approaches such as:
- separating the formulas in two columns, but I don't have an operator that
concatenates this kind of formulas
- the last try: I've tried to calculate (successfully) the number of the
column in which the first value different from zero appears, but I don’t know
any formula that, from this number, goes to that specific column, and returns
the value in it.
I don't know what to do.
Thanks for all the help you can give me.

 

[Roger Govier]

Hi

Try something like the following array entered formula
{=BZ4/INDEX($I$4:BY4,MIN(IF($I$4:BY4<>0,COLUMN($I4:$I4)-9)))}

To array enter the formula, commit or Edit using Control+Shift+Enter
(CSE) not just Enter.
Do not type the curly braces { } yourself, if you use CSE, Excel will
enter than for you.

What this formula does is to find the first entry in the range I4:BY4 to
divide into the value in BZ4.
There is no need for IF's, as it is finding the first value in the
range.

in your case, if there is the likelihood of there being no value before
BZ4, there would need to be 1 If statement

{=IF(INDEX($I$4:BY4,MIN(IF($I$4:BY4<>0,COLUMN($I4:$I4)-9)))=0,1,
BZ4/INDEX($I$4:BY4,MIN(IF($I$4:BY4<>0,COLUMN($I4:$I4)-9)))}

 

[LadyHawk]

No it didn't work because the cells that have the price cost aren't next to
each other.
($I$4:BY4). They are specifc cells:
- August is in column X;
- September is in column AG;
- October is in column AP;
- November is in column AY;
- December is in column BH;
- January is in column BQ;
- February is in column BZ.
But thank you for your help.

 

[Harlan Grove]

No it didn't work because the cells that have the price cost aren't next to
each other.
($I$4:BY4). They are specifc cells:
- August is in column X;
- September is in column AG;
- October is in column AP;
- November is in column AY;
- December is in column BH;
- January is in column BQ;
- February is in column BZ.

....

Then make them into an array.

X4*{1,0,0,0,0,0,0}+AG4*{0,1,0,0,0,0,0,0,}+AP4*{0,0,1,0,0,0,0}+AY4*{0,0,0,1,0,0,0}
+BH4*{0,0,0,0,1,0,0}+BQ4*{0,0,0,0,0,1,0}+BZ4*{0,0,0,0,0,0,1}

Or, since these appear to be every 9th column, use something like

INDEX($P$4:$BZ$4,9*<some.expression.that.increments.by.1>)