Dynamic IF calculations

[Charles_]

<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; color: #454545 } p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; color: #454545 ; min-height: 14.0px} p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica; min-height: 14.0px} table.t1 {border-collapse: collapse} td.td1 {border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #aaaaaa #aaaaaa #aaaaaa #aaaaaa ; padding: 1.0px 5.0px 1.0px 5.0px} </style>Hi all,


I've been struggling on how to best convey my question by using the table below. Can you help me work out a formula for column B below, please?


Thank!




[TABLE="class: t1"]
<tbody>[TR]
[TD="class: td1"]

[/TD]
[TD="class: td1"]A
[/TD]
[TD="class: td1"]B
[/TD]
[TD="class: td1"]c
[/TD]
[/TR]
[TR]
[TD="class: td1"]1
[/TD]
[TD="class: td1"]Completed
[/TD]
[TD="class: td1"]Average
[/TD]
[TD="class: td1"]Projected - dynamically calculated
[/TD]
[/TR]
[TR]
[TD="class: td1"]2
[/TD]
[TD="class: td1"]20
[/TD]
[TD="class: td1"]=A2
[/TD]
[TD="class: td1"]=B2*4
[/TD]
[/TR]
[TR]
[TD="class: td1"]3
[/TD]
[TD="class: td1"]25
[/TD]
[TD="class: td1"]=SUM(A3+A2)/2
[/TD]
[TD="class: td1"]=B3*4
[/TD]
[/TR]
[TR]
[TD="class: td1"]4
[/TD]
[TD="class: td1"]

[/TD]
[TD="class: td1"]if A4 is blank, use latest available average in column B, in this case B3


If there is a value in A4:
=SUM(A4+A3+A2)/3
[/TD]
[TD="class: td1"]=B4*4
[/TD]
[/TR]
[TR]
[TD="class: td1"]5
[/TD]
[TD="class: td1"]

[/TD]
[TD="class: td1"]if A5 is blank, use latest available average in column B, in this case B3


If there is a value in A5:
=SUM(A5+A4+A3+A2)/4
[/TD]
[TD="class: td1"]= B5*4
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Indystick]

=IF(ISBLANK(A4),B3,AVERAGE($A$2:A4))<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; color: #454545 } p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; color: #454545 ; min-height: 14.0px} p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica; min-height: 14.0px} table.t1 {border-collapse: collapse} td.td1 {border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #aaaaaa #aaaaaa #aaaaaa #aaaaaa ; padding: 1.0px 5.0px 1.0px 5.0px} </style>

 

[Fluff]

In B2 & fill down
=AGGREGATE(1,6,A$2:A2)

 

[Charles_]

In B2 & fill down
=AGGREGATE(1,6,A$2:A2)

Thanks Fluff, almost works like I needed!

The only issue I have is if I don't have a value on B2, then the formula breaks. I realise in the example above I had a value but I won't always have one, is this something you can help me with?

 

[Fluff]

How about
=IFERROR(AGGREGATE(1,6,A$2:A2),"")

 

[Charles_]

Thanks! that worked great!

 

[Fluff]

You're welcome