Formula help - Repeat caculation until condition is met and perform another calculation when it is

[TekWarfare]

I really don't know how to word the title

Task:

I have a column of frames (1, 2, 3,...) and a column of time elapsed (33.835, 59.944, 85.267,...)ms.
I would like to count the number of frames every second, i.e. average FPS.


Attempt (vague pseudo code):

Start at cell B2, move down a cell to B3 and calculate the difference in time elapsed (B3-B2), if it isn't 1000ms move down to the next cell, B4, and calculate difference in "time elapsed" (B4-B2) and so on.


Once the difference in time elapsed is 1000ms, the difference in frames between the starting cell B2 and ending cell, say B39, is calculated (A39-A2) and output in an adjacent column.


Problems:
The difference in time elapsed will rarely be one second (1000ms) exactly so the formula would need to determine which cell gives the closest value to 1000ms, say cell B38 or B39.


The formula needs to perform this again starting from the last cell, i.e. now we start from B39 and do the same again.


I don't know how to code this, all I know is that it needs an IF statement, but I don't really know what I'm doing.



Data:
Google Sheets: https://docs.google.com/spreadsheets/d/1Nn6EaxpgMCEve0tCpmFnENPYmIMcU-g5tZnw81XqEvA

Any help greatly appreciated.

 

[Eric W]

Welcome to the forum.

First of all, if your spreadsheet is on Google Sheets, I can't guarantee that these formulas will work. They should, but I don't work on Google Sheets, and I have no way to test them.

Next, consider this layout:

	A	B	C	D	E	F	G
frame	ms	Sum	Second	FPS	Average FPS	Single cell formula

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

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]

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

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]300[/TD]

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

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]600[/TD]

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

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]201[/TD]
[TD="align: right"]801[/TD]

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

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]1167[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]1217[/TD]

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

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]777[/TD]
[TD="align: right"]1994[/TD]

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

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]2117[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]444[/TD]
[TD="align: right"]2561[/TD]

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

[TD="align: center"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]872[/TD]
[TD="align: right"]3433[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]355[/TD]
[TD="align: right"]3788[/TD]

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

[TD="align: center"]13[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]422[/TD]
[TD="align: right"]4210[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]811[/TD]
[TD="align: right"]5021[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]257[/TD]
[TD="align: right"]5278[/TD]

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

[TD="align: center"]16[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]732[/TD]
[TD="align: right"]6010[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]6133[/TD]

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

[TD="align: center"]18[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]6232[/TD]

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

[TD="align: center"]19[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]345[/TD]
[TD="align: right"]6577[/TD]

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

[TD="align: center"]20[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]6675[/TD]

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

[TD="align: center"]21[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]6807[/TD]

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

[TD="align: center"]22[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]6918[/TD]

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

[TD="align: center"]23[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]7129[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=SUM($B$2:B2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IF(SUM($B$2:$B2)>=(MAX($D$1:$D1)+1)*1000,MAX($D$1:$D1)+1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=IF(D2="","",ROW()-LOOKUP(2,1/($D$1:$D1<>""),ROW($D$1:$D1)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=AVERAGE(E:E)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=MAX(A:A)/(SUM(B:B)/1000)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]





Columns A and B are as you describe. Column C is merely a running total of column B. It's not necessary, except to explain. Column D looks at the running total, and when it ticks over another 1000, it marks the second. It is somewhat imprecise, but the remainder is considered for when the next second is marked. Column E is a basic FPS calculator, just counting the number of rows between seconds. Then the F2 formula averages column E. This could also be accomplished with the single-cell formula in G2, which is slightly different due to the sum at the end not being an exact multiple of 1000.

Hope this helps.

 

[TekWarfare]

Thank you very much for the very thorough reply, Eric and sorry for my delayed one.

I don't use google sheets either, but I figured it was an easy way to host some sample data to aid my question.

Since I am new to excel formulas and struggle with programming I did spend the best part of 2 hours figuring out how these formulas work. I had to research and understand absolute and relative cell references and the MAX function. If I understand correctly this is how the formulas work:

=IF(SUM($B$2:$B2)>=(MAX($D$1:$D1)+1)*1000,MAX($D$1:$D1)+1,"")

The IF statement follows the standard pattern of: IF(logic test, value if true, value if false)

SUM($B$2:$B2)
The first part of the logic test is a sum taken from the absolute column and absolute row $B$2 to the absolute column but relative row $B2, this allows the formula to be "copied-down" so each cell has a sum formula summing up to it, i.e. at cell D39 we would be summing from B2 to B39.

(MAX($D$1:$D1)+1)*1000
The next part of the logic test involves the MAX function. Starting from the absolute column and absolute row $D$1 and looking though column D by use of the absolute column but relative row $D1 the formula can again be "copied-down" the rest of the column. As an example, cell D39 would look at the values from D1 to D39 and the MAX function determines which of those values is greatest.
1 is added to the result from MAX so the number of seconds increases by one and thus counts time incrementally.
The result is finally multiplied by 1000 to turn the answer into milliseconds.

>=
"Greater than or equal to". The actual logic test looks at SUM($B$2:$B2) and (MAX($D$1:$D1)+1)*1000 and determines whether this is true or false for each cell in the column. If true, the result of MAX($D$1:$D1)+1 is shown in the relevant cell. If false, "" means that nothing is output to that cell.

=IF(D2="","",ROW()-LOOKUP(2,1/($D$1:$D1<>""),ROW($D$1:$D1)))
It'd take me another few hours to understand how the formula for E2 works, so I may come back to this at a later date.

=AVERAGE(E:E)
F2's formula makes use of the AVERAGE function and simply takes the mean average of column E, by adding them and dividing by the number of elements.

=MAX(A:A)/(SUM(B:B)/1000)

The SUM(B:B) takes the sum of all cells in column B, i.e. the total time elapsed in ms. It is then divided by 1000 to get an answer in seconds.
MAX(A:A) looks at column A and determines the largest value, i.e. the total number of frames of drawn. The result is divided by the elapsed time in seconds to give a mean average frames per second value that looks at the whole data, this is a more accurate answer than that of cell F2.

I assume it's just a typo or mistake, but one question for now is why do your references to column D start at row 1 instead of row 2? MAX($D$1:$D1) for example, shouldn't it be MAX($D$2:$D2) or am I missing something?

Once again, thank you very much for the answer.

 

[Eric W]

Nope, not a typo! The formula is in cell D2. If I put a reference to D2 in the formula, then it becomes a circular reference, which Excel usually doesn't like. To avoid that, I start at row 1, and I also take into account that the MAX function ignores non-numeric values, so it will ignore the header in D1.

As far as the E2 formula, here are some tips, since I'm using LOOKUP in a way that's not well documented. I want to find the row of the cell furthest down in the range that is populated. For example, if it's in E11, I want to know what row above 16 has a non-empty cell in column D, which is D9. The formula in E11 is:

=IF(D11="","",ROW()-LOOKUP(2,1/($D$1:$D10<>""),ROW($D$1:$D10)))

LOOKUP has some array processing capabilities, which is a huge subject, but in essence it works on a range of data. The 2 places in the formula that have the D1:D10 range are replaced like this:

=IF(D11="","",ROW()-LOOKUP(2,1/({"","","","","",1,"","",2,""}<>""),{1,2,3,4,5,6,7,8,9,10}))

The next thing it does is evaluate the <> (not equals) condition on each value in the first array, giving:

=IF(D11="","",ROW()-LOOKUP(2,1/({FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE}<>""),{1,2,3,4,5,6,7,8,9,10}))

Now it evaluates the 1/ part on each value in the first array. When working with TRUE/FALSE values in an arithmetic way, TRUE = 1, and FALSE = 0, so we get:

=IF(D11="","",ROW()-LOOKUP(2,{#DIV/0,#DIV/0,#DIV/0,#DIV/0,#DIV/0,1,#DIV/0,#DIV/0,1,#DIV/0},{1,2,3,4,5,6,7,8,9,10}))

Now we're back to the main documented use of LOOKUP, which is trying to find a value in an array. We're looking for a 2, which we know we'll never get, since the only 2 possible values are 1 and #DIV/0. But LOOKUP ignores error values, and it keeps searching left to right until it finds a number greater than 2. If it never does, it points to the location with the last numeric value in the array, then takes the value from the second array in the same position. Giving:

=IF(D11="","",ROW()-9)

ROW() points to the current row, or 11, meaning the end result is 2.


Incidentally, very nice job in figuring out the other formulas. Use the Evaluate Formula tool on the Formulas tab to help. Good luck!

 

[Aladin Akyurek]

[...]
Now we're back to the main documented use of LOOKUP, which is trying to find a value in an array. We're looking for a 2, which we know we'll never get, since the only 2 possible values are 1 and #DIV/0. But LOOKUP ignores error values, and it keeps searching left to right until it finds a number greater than 2. If it never does, it points to the location with the last numeric value in the array,
[...]

This reads as if LOOKUP examines every cell from left to right. It does not do such a thing, otherwise it would be slow. We know it's not.

Here is a non-MS document on the usage recruited in this thread:
https://www.mrexcel.com/forum/excel-questions/99621-lookup-value-unsorted-data.html#post492425

Here are attempts at understanding the behavior of LOOKUP and kindred functions:

https://groups.google.com/forum/#!topic/microsoft.public.excel.worksheet.functions/WAspQoumocc

https://www.mrexcel.com/forum/excel...tiple-matches-match-returned.html#post1523998

 

[Eric W]

Thank you for those links. I read them with great interest, and I'll bookmark them for future reference. I agree completely that the underlying algorithm must be some variant of a binary search. I'd expect no less of Microsoft, and the speed of the function bears that out. However, it can't be a vanilla binary search because it allows the inclusion of error values. How those are handled, I wouldn't venture to say. Rather than get bogged down into details (which I don't actually know), I used the imprecise, but understandable "searching from left to right". That is the effect, if not the actual method.

Thanks again for your input.

 

[Archie Way]

Thank you for those links. I read them with great interest, and I'll bookmark them for future reference. I agree completely that the underlying algorithm must be some variant of a binary search. I'd expect no less of Microsoft, and the speed of the function bears that out. However, it can't be a vanilla binary search because it allows the inclusion of error values. How those are handled, I wouldn't venture to say. Rather than get bogged down into details (which I don't actually know), I used the imprecise, but understandable "searching from left to right". That is the effect, if not the actual method.

Thanks again for your input.

Hi Eric,

Are you able to offer any help with my question on this link? It seems quite similar.
Thank you in advance.

Formula help please.

Hello, I hope you can help me figure out a formula issue I am having. On my spreadsheet I have a tab called 'main' which shows item code, description, category, current stock figures, quantity on customer orders, a column where I'd like my formula to go and last column is standard lead time in...

www.mrexcel.com