if expression within another if

[#1]

I'm trying to put multiple conditions "if" inside another "if", where this condition have values ​​in the years 2012, 2013 and 2014, but if 2014 is zero the formula should be made using the values ​​of 2012 and 2013, put up in 2014 is not zero the formula must be made using 2013 and 2014.

As follows:

if(if(ANO=year(today()),VALOR_CONTABIL=0,sum(if(ANO=year(today())-1,VALOR_CONTABIL))/sum(if(ANO=year(today())-2,VALOR_CONTABIL)))-1), (sum(if(ANO=year(today()),VALOR_CONTABIL))/sum(if(ANO=year(today())-1,VALOR_CONTABIL)))-1)

[#2]

Let the answer to a solution

Selecting an isolated year 2012 or 2013 or 214, the return shall be zero (0)

Selecting 2012 x 2013, the formula does the calculation between (2013/2012),

Selecting 2012 x 2014, the formula does the calculation between (2014/2012),

Selecting 2013 x 2014, the formula does the calculation from 2014/2013.

[jerem1234]

Try this one and see if it fits your needs.

=if(GetSelectedCount(ANO)=0 or GetSelectedCount(ANO)>2,

if(sum({<ANO = {$(=max(ANO))}>}VALOR_CONTABIL)=0,sum({<ANO = {$(=max(ANO)-1)}>}VALOR_CONTABIL)/sum({<ANO= {$(=max(ANO)-2)}>}VALOR_CONTABIL)-1,sum({<ANO = {$(=max(ANO))}>}VALOR_CONTABIL)/sum({<ANO = {$(=max(ANO)-1)}>}VALOR_CONTABIL)-1),

if(sum({<ANO = {$(=max(ANO))}>}VALOR_CONTABIL)<>0, sum({<ANO = {$(=max(ANO))}>}VALOR_CONTABIL)/sum({<ANO= {$(=min(ANO))}>}VALOR_CONTABIL)-1, 0))

Hope this helps!

[#4]

Thank you friend.

This formula did the calculation correctly.

[#5]

Jerem friend

There's one more detail that this formula must include, in addition to any conditions that the formula considers to do the calculations, we have one more:

Totals (2012, 2013, 2014) are classified by segment, ie, segment 1, 2, 3. Each one means a product line, and the formula has to respeirar segment included in the formula to make all other calculations.

example:

='TOTAL:  '&Num(if(LINHA='2', IF(GetSelectedCount(ANO)=0 or GetSelectedCount(ANO)>2,

  if(sum({<ANO={$(=max(ANO))}>}QTDE_LITROS_CAIXA/100)=0,

  (sum({<ANO={$(=max(ANO)-1)}>}QTDE_LITROS_CAIXA/100)/sum({<ANO={$(=max(ANO)-2)}>}QTDE_LITROS_CAIXA/100)-1)*100,

  (sum({<ANO={$(=max(ANO))}>}QTDE_LITROS_CAIXA/100)/sum({<ANO={$(=max(ANO)-1)}>}QTDE_LITROS_CAIXA/100)-1)*100),

  if(sum({<ANO={$(=max(ANO))}>}QTDE_LITROS_CAIXA/100)<>0,

  (sum({<ANO={$(=max(ANO))}>}QTDE_LITROS_CAIXA/100)/sum({<ANO={$(=min(ANO))}>}QTDE_LITROS_CAIXA/100)-1)*100,0)),0),'##,#')&'%'

[#6]

In this example if I do not select any segment the number is zero, however the formula should respect the segment in-built in it and return the correct calculation.

[#7]

Good morning, already managed to fix this formula.

See how it fits:

='TOTAL:  '&Num (if(GetSelectedCount(ANO)=0 or GetSelectedCount(ANO)>2,

  if(sum({<ANO={$(=max(ANO))}>}IF(LINHA='2',QTDE_LITROS_CAIXA/100))=0,

  (sum({<ANO={$(=max(ANO)-1)}>}IF(LINHA='2',QTDE_LITROS_CAIXA/100))/sum({<ANO={$(=max(ANO)-2)}>}IF(LINHA='2',QTDE_LITROS_CAIXA/100))-1)*100,

  (sum({<ANO={$(=max(ANO))}>}IF(LINHA='2',QTDE_LITROS_CAIXA/100))/sum({<ANO={$(=max(ANO)-1)}>}IF(LINHA='2',QTDE_LITROS_CAIXA/100))-1)*100),

  if(sum({<ANO={$(=max(ANO))}>}IF(LINHA='2',QTDE_LITROS_CAIXA/100))<>0,

  (sum({<ANO={$(=max(ANO))}>}IF(LINHA='2',QTDE_LITROS_CAIXA/100))/sum({<ANO={$(=min(ANO))}>}IF(LINHA='2',QTDE_LITROS_CAIXA/100))-1)*100,0)),'##,##')&'%'