Nested 'IF' formula to calculate percentage differences of values in a Column

[Stripez3]

=IFERROR(IF(C2="","0",IF(C2+C3>3,1-(C3/C2),IF(C2+C4>3,1-(C4/C2),IF(C2+C5>3,1-(C5/C2),IF(C2+C6>3,1-(C6/C2),IF(C2+C7>3,1-(C7/C2))))))),"0")

- My current formula does not fully work as I want. I tried nested IF statements but there has to be a better way or corrections to make this formula work as I need.

- I need the formula to first check the current cell "C2" of Column C and see if it is empty. This functions currently works in the formula.

- If the cell is empty it will return a "0".

- If the cell "C2" in Column C has a value it will look to the next cell below "C3" and give the percentage difference between the two values "E2" Column E.

- Currently in the formula entering a value in the first two cells everything works, but if you put a value in the first cell and the third cell it does not work.

- I need help with the formula to take the first value and any other value in Column C and give the percentage difference between the two values.

- As an example looking at the current Column C the formula will not add up the percentages of the first and third values.

<tbody>
</tbody>

-Link to excel sheet

 

[Scott Huish]

How about:

=IF(C2="",0,IF(COUNT(C2:C7)>=2,1-(C2/SUM(C3:C7)),0))

or if there will only be 2 numbers C2 and 1 other:

=IF(COUNT(C2:C7)=2,1-(C2/SUM(C3:C7)),0)

 

[skywriter]

Excel 2010
	A	B	C	E
2	2.30	>>>	2.30	8.26%
3		>>>	0.00
4	2.11	>>>	2.11
5		>>>	0.00
6		>>>	0.00
7		>>>	0.00
IF Nested Formula

Cell Formulas
Range	Formula
C2	=IF(A2>1,A2,0)
C3	=IF(A3>1,A3,0)
C4	=IF(A4>1,A4,0)
C5	=IF(A5>1,A5,0)
C6	=IF(A6>1,A6,0)
C7	=IF(A7>1,A7,0)
E2	=IFERROR(IF(C2="",0,IF(C2+C3>3,1-(C3/C2),IF(C2+C4>3,1-(C4/C2),IF(C2+C5>3,1-(C5/C2),IF(C2+C6>3,1-(C6/C2),IF(C2+C7>3,1-(C7/C2))))))),0)

 

[Stripez3]

Scott,
Thanks so much for your help. I have been trying to solve this problem for a while with no luck and resorted to the expert boards to solve. I tried the formula and its close to what I need but yet still not right. It works partially but I did not fully explain in my initial post how this should work so let me try again and hopefully you can solve this. Your two formulas did partially do what I was looking for but when there are more than two values entered it added them all or did not calculate the values when three cells were populated.

Is it possible that the formula can calculate through Column 'C' that cell 'C2' and the next first value (in any cells ranging from C3 to C7) in Column C can be added... regardless if there are multiple entries in this Column? So, 'C2' and the first cell populated with a value in this Column is calculated only. For example (see below), there could be four entries in Column 'C' but the formula will grab the value of 'C4' because its the first of the values in this column. ...and if you take out the value of 'C4' the formula would look for the next first value in this column ie. 'C5' etc...

Example:

C2 2.30
C3
C4 2.25
C5 2.20
C6
C7 2.15

The other restraint and perhaps the problem with my initial formula that does not work right because... that the cells in Column 'C' have formulas in them and they must remain the same and read as "" (blank out) =IF(A2>1,A2,"") when no values are entered. In the Excel worksheet that I am using other cells and formulas linked to this column do not work right if I change the formula in the column to show and read as (Zero - 0).

Look forward to your response.

Kevin

How about:

=IF(C2="",0,IF(COUNT(C2:C7)>=2,1-(C2/SUM(C3:C7)),0))

or if there will only be 2 numbers C2 and 1 other:

=IF(COUNT(C2:C7)=2,1-(C2/SUM(C3:C7)),0)

 

[Stripez3]

Bruce,

The issue here that I have to solve is perhaps what I did not fully explain... So I will try again. The cell formulas in C2-C7 have to read out as (blank - ""). I see that if you change them to ( Zero 0 ) the formula works and does exactly what I want. The issue here is how or what formula works in this context and performs the same function/calculations yet still keeping the those cell formulas in the C2-C7 range the same. Other formulas in the excel worksheet linked to this column do not work or show right when they are changed to (Zero 0 ).

Hope you can help solve... Thanks

Kevin

 

[Aladin Akyurek]

What is the output (figures) you want to see when:

C2 2.30
C3
C4 2.25
C5 2.20
C6
C7 2.15

in say D2:D7?

 

[Stripez3]

Hi Aladin,

If you read up at my original post there is the full post of what I am looking for an an example excel sheet.
The output should be linked into one cell 'E2' that gives you the percentage difference between the two values. For example C2 - 2.30 and C4 2.25 = -2.2% when this is calculated.

Does this help you? Thanks

Kevin

 

[skywriter]

Is this closer?

=IF(C2="",0,IF(AND(ISNUMBER(C3),SUM(C2,C3)>3),1-(C3/C2),IF(AND(ISNUMBER(C4),SUM(C2,C4)>3),1-(C4/C2),IF(AND(ISNUMBER(C5),SUM(C2,C5)>3),1-(C5/C2),IF(AND(ISNUMBER(C6),SUM(C2,C6)>3),1-(C6/C2),IF(AND(ISNUMBER(C7),SUM(C2,C7)>3),1-(C7/C2)))))))

Excel 2010

	A	B	C	D	E
Enter Values	Col w/Formulas	Percentage Differences

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #95B3D7, align: center"]2.30[/TD]
[TD="align: center"]>>>[/TD]
[TD="bgcolor: #C4D79B, align: center"]2.30[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DA9694, align: center"]3.48%[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #95B3D7, align: center"][/TD]
[TD="align: center"]>>>[/TD]
[TD="bgcolor: #C4D79B, align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #95B3D7, align: center"][/TD]
[TD="align: center"]>>>[/TD]
[TD="bgcolor: #C4D79B, align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #95B3D7, align: center"][/TD]
[TD="align: center"]>>>[/TD]
[TD="bgcolor: #C4D79B, align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #95B3D7, align: center"][/TD]
[TD="align: center"]>>>[/TD]
[TD="bgcolor: #C4D79B, align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #95B3D7, align: center"]2.22[/TD]
[TD="align: center"]>>>[/TD]
[TD="bgcolor: #C4D79B, align: center"]2.22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

IF Nested Formula​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=IF(C2="",0,IF(AND(ISNUMBER(C3),SUM(C2,C3)>3),1-(C3/C2),IF(AND(ISNUMBER(C4),SUM(C2,C4)>3),1-(C4/C2),IF(AND(ISNUMBER(C5),SUM(C2,C5)>3),1-(C5/C2),IF(AND(ISNUMBER(C6),SUM(C2,C6)>3),1-(C6/C2),IF(AND(ISNUMBER(C7),SUM(C2,C7)>3),1-(C7/C2)))))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=IF(A2>1,A2,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]=IF(A3>1,A3,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]=IF(A4>1,A4,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]=IF(A5>1,A5,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C6[/TH]
[TD="align: left"]=IF(A6>1,A6,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C7[/TH]
[TD="align: left"]=IF(A7>1,A7,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Hi Aladin,

If you read up at my original post there is the full post of what I am looking for an an example excel sheet.
The output should be linked into one cell 'E2' that gives you the percentage difference between the two values. For example C2 - 2.30 and C4 2.25 = -2.2% when this is calculated.

Does this help you? Thanks

Kevin

How about [C4,C5] and [C5,C7]?

 

[Scott Huish]

=IF(COUNT(C2:C7)>1,1-(C2/INDEX(C3:C7,MATCH(TRUE,ISNUMBER(C3:C7),0))),0)

Confirm with CTRL-SHIFT-ENTER rather than just Enter.