Formula to account fo a moving data range

[jharcourt1]

Hi all,

My first post so be gentle :D

I have/ require a spreadsheet at work that is used to 'sign' people off in terms of authorties based on a set of results.

The criteria is:

- A minimum of 50 cases must be complete
- After 50 cases to 'pass' the accuracy level must be 95% of higher (realistically this could only therefore be 96, 98 or 100%)
- 4 outcomes, Pass, and three levels of fail (only pass counts)

The issue i am having is that i require a formula for arguments sake in cell A1 that is the % of passed cases in the last 50. There is my sticking point. Yes it easy to adapt a COUNTIF formula to give the last 50, but i'd rather this was an automated process once for example a result is recorded for case 51.

Is this possible? Have i explained myself well enough?

Thanking anyone who has any ideas!

James

[:) Sixthsense :)]

Please attach a sample workbook with expected output for better understanding

[jharcourt1]

Hi,

I have attached a very basic exmaple of what I mean.

So the answer in Cell E2, needs to be the percentage of the last 50 inputs in column C where the answer is 'P' (for passed).

Does that help understand my issue?

Thank you

[jharcourt1]

Can anyone help with this at all?

Pretty please?

[:) Sixthsense :)]

What do you mean by last 50 cases?

[jharcourt1]

So you see the Case# column running down B, extends for say 100 'cases' that are being marked (like exam papers for example). What i need is Cell E2 to display the running percentage of only the last 50 cases. so the percentage of case 1-50, but then as soo as case 51 is completed it would need to calculate the percentage of 2-51 (so the result is only the percentage of 50 cases).

Does that make more sense?

Thank you so much for any help you can provide.

James

[:) Sixthsense :)]

Which is the column needs to be used for finding the percentage? please show the expected output also

[jharcourt1]

The column is column C, i want the percentage of 'P' as the outcome, over the last 50 cases only.

thank you

[jharcourt1]

Has anyone got any thoughts on this?

I am really struggling, but it must be possible...

[:) Sixthsense :)]

You will get quick solution if you show the expected output also

[jharcourt1]

If i could show the expected output i wouldnt need help?

I dont know how more i can explain it?

[Lemice]

Hello,

You can try this formula

Please Login or Register  to view this content.

You can just paste it on D1

Here is a sample file with the formula.

[jharcourt1]

That is absolutely SPOT on.

Thank you so much Lemice!!

[jharcourt1]

Lemice, can you briefly explain how this formula works so i can apply it to my actual data?

thank you

[Lemice]

On the column containing "P" or something else, I used MATCH to find the biggest row number with data. Assuming that Column is B in the sample file, it will be like this

Please Login or Register  to view this content.

What this formula does is, lookup a word consisting of 255 letter "z" (which will never be found), but return the approximated row number, not exact, thus it will return the closest row to the end of the worksheet, or you can say the last row with data in column B.

Let's called what we just found X, then the COUNTIF is really simple
=COUNTIF(INDIRECT("C"& X - 49 & ":C" & X ),"P")/50

The INDIRECT is a formula to assemble together a reference.
Assuming your last row with "P" or something else on Column B, X = 53, then the INDIRECT will assemble "C" with "4" (53 - 49), with ":C" and "53", then you have "C4:C53", last 50 rows!

And the last job belongs to COUNTIF. What it does is what you think it is - the good old COUNTIF will count the number of "P" on the reference assembled by INDIRECT. the /50 at the end of the formula is to divide the results with 50, turning it into percentage.

If you still have trouble, can you provide a small sample of your live file? You can change the name and all personal data, just make it like "insert something here" or "Bob" or even "Iron man". And just about couple of rows will do it (at least 50!)