Average only last two number in a row

[jmh1983]

So I currently have this formula:
=IF(AC61="","",IF(AC61<9.5,"",IF(AC61="No Review",IF(AB61="No Review",IF(AA61="No Review","","No Review"),"No Review"),"No Review")))

This looks at the last 4 weeks of data and if it finds a value over 9.5, "No Review" is populated in the cell. If it's below 9.5, the cell will become blank.

What I need the formula to do is to average the first two numbers it comes across (right to left), and give the same end result as the formula posted above.

 

[BobUmlas]

If your data extends through AC as the maximum column, this formula will work, but it must be entered via ctrl/shift/enter:
=IF(AVERAGE(N(OFFSET($A1,0,LARGE(COLUMN(A:AC)*ISNUMBER(A1:AC1)-1,{1,2}))))>9.5,"No Review","")
This works for row 1. You can fill it down once it's been entered, or to start in row 2:
=IF(AVERAGE(N(OFFSET($A2,0,LARGE(COLUMN(A:J)*ISNUMBER(A2:J2)-1,{1,2}))))>9.5,"No Review","")
etc.

 

[jmh1983]

If your data extends through AC as the maximum column, this formula will work, but it must be entered via ctrl/shift/enter:
=IF(AVERAGE(N(OFFSET($A1,0,LARGE(COLUMN(A:AC)*ISNUMBER(A1:AC1)-1,{1,2}))))>9.5,"No Review","")
This works for row 1. You can fill it down once it's been entered, or to start in row 2:
=IF(AVERAGE(N(OFFSET($A2,0,LARGE(COLUMN(A:J)*ISNUMBER(A2:J2)-1,{1,2}))))>9.5,"No Review","")
etc.

Thanks for the response. I've entered the following into cell AD4:
=IF(AVERAGE(N(OFFSET($C4,0,LARGE(COLUMN(C:BC)*ISNUMBER(C4:BC4)-1,{1,2}))))>9.5,"No Review","") via ctrl/shift/enter

This returns a value of "0" (It should return either a blank cell or "No Review")

Data begins in cell C4 and is currently set to run to cell BC4.

Thanks again!

 

[barry houdini]

I'm not quite sure how you would get a zero from that formula, it should return either a blank or "No Review". Have you got any circular references, that sometimes causes formulas to erroneously return zero?

It seems that you want to average the last two numbers in the row - if you don't have any gaps in the data this non-array version should work for you

=IF(AVERAGE(OFFSET(C4,0,MATCH(9.99E+307,C4:BC4)-2,1,2))>9.5,"No Review","")

 

[jmh1983]

Perhaps, I'm not explaining the layout of the worksheet well enough, my fault. I wish I knew how to post a screenshot to the thread, might explain a bit better. For example:
Cell Y4 contains the number 10. The next three weeks "no review" is populated into the cells. Cell AC4 contains a 9.8. Cell AD4 should have a formula that says something to the effect of "Since the average of the last two numbers in the row (Y4 and AC4) are greater than 9.5, "No Review" should be populated in AD4, AE4, and AF4. If the number 9 were entered in AG4, AH should come up blank, indicating that another review is needed because the average of the last two numbers has dropped below 9.5


Clear as mud?

 

[BobUmlas]

You changed my formula! You changed the $A1 to $C4. It was right to change the 1 to a 4, but wrong to change the $A to $C. Try again with $A4. (It will ALWAYS be $A... doesn't matter where the rest of the data is). Remember ctrlshift/enter.

 

[jmh1983]

Haha, my apologies! I assumed (incorrectly) that since my data begins in column C, that's where this would need to start. So I've entered as follows:
=IF(AVERAGE(N(OFFSET($A4,0,LARGE(COLUMN(A:BC)*ISNUMBER(A4:BC4)-1,{1,2}))))>9.5,"No Review","")

This was entered into cell AD4. (Excuse my ignorance here), but I pasted as normal, then highlighted the formula with ctrlshift/enter to get the brackets. I am still getting a cell value of "0" here.

Thanks for all of your help so far!

 

[BobUmlas]

Haha, my apologies! I assumed (incorrectly) that since my data begins in column C, that's where this would need to start. So I've entered as follows:
=IF(AVERAGE(N(OFFSET($A4,0,LARGE(COLUMN(A:BC)*ISNUMBER(A4:BC4)-1,{1,2}))))>9.5,"No Review","")

This was entered into cell AD4. (Excuse my ignorance here), but I pasted as normal, then highlighted the formula with ctrlshift/enter to get the brackets. I am still getting a cell value of "0" here.

Thanks for all of your help so far!

===================YOU CAN'T Enter this into AD4, since you'll get an error because AD4 is inside A4:BC4 and you get a circular reference! Put this formula OUTSIDE of column BC, perhaps BD4. You also can't "highlight the formula with ctrl/shift/enter" -- no such thing. You press the F2 key (putting you in edit mode inside the formula, THEN press ctrl+shift+enter.This formula works. You're just not doing something exactly like I'm describing. Good luck!

 

[jmh1983]

BobU,

Thanks, I have it working now within column C (just made it easier to always view). The array formula is also no longer a mystery to me.

The next part should be a bit simpler, but alas... still a mystery as to how to make it work. I'll try and paint a picture:
Cell C4 contains your formula, which coincidentally now returns "Review 4 weeks", or "Review 2 weeks" depending on the value.

The last score is 8.0, in cell AD4. C4 returns "review 2 wks" as it should. AE4, I have set as "exempt" as every other week will be exempt from scoring no matter what. I would like AF4 to say "Review", as this is 2 weeks from the last score. If the score entered in AF4 is still below the threshold, AH4 should say review, but if it is above it, AH4 should say exempt, and AJ4 should say "review." (This is 4 weeks from a good score in AF4)

Thanks so much. I hope this makes sense.