Formula Help... Thanks Tom
 

Hi… I have a need for a formula that has me stumped… The first Colum is
user entry of products used per day with a entry total in row 33 from
the sum of 2 to 32 in column a. The second column is user entry for
broken (unusable product) for that day with a total in row 33 from the
sum of 2 to 32 in column b. The third column is the percentage of
unusable product verses total product per user entry (Example B2/A2)
with a total average percentage of broken product to usable product in
row 33 from the average of column c rows 2 thru 32. This was very
simple but now I need a cell to provide me with the percentage of broke
products for the last 1000 entries, not using any of the entered
information above 1000 products used…Any help would be much
appreciated… I hope you understand my gibberish…So what I am looking
for is no mater the QTY user entered I will get the % of broken product
for only the last 1000 products entered or up to the first 1000 products
entered in column A….Respectfully Tom


--
americanenergy
------------------------------------------------------------------------
americanenergy's Profile: http://www.excelforum.com/member.php...o&userid=30564
View this thread: http://www.excelforum.com/showthread...hreadid=502130

Formula Help... Thanks Tom
 

I'm confused. If you only have products in ro2 2-32, how do average the
last 1000?

--

HTH

RP

"americanenergy"
<americanenergy.21sicz_1137519907.4604@excelforu m-nospam.com wrote in
message news:americanenergy.21sicz_1137519907.4604@excelfo rum-nospam.com...

Hi. I have a need for a formula that has me stumped. The first Colum is
user entry of products used per day with a entry total in row 33 from
the sum of 2 to 32 in column a. The second column is user entry for
broken (unusable product) for that day with a total in row 33 from the
sum of 2 to 32 in column b. The third column is the percentage of
unusable product verses total product per user entry (Example B2/A2)
with a total average percentage of broken product to usable product in
row 33 from the average of column c rows 2 thru 32. This was very
simple but now I need a cell to provide me with the percentage of broke
products for the last 1000 entries, not using any of the entered
information above 1000 products used.Any help would be much
appreciated. I hope you understand my gibberish.So what I am looking
for is no mater the QTY user entered I will get the % of broken product
for only the last 1000 products entered or up to the first 1000 products
entered in column A..Respectfully Tom


--
americanenergy
------------------------------------------------------------------------
americanenergy's Profile:
http://www.excelforum.com/member.php...o&userid=30564
View this thread: http://www.excelforum.com/showthread...hreadid=502130

Formula Help... Thanks Tom
 

Tom:

I don't think it's possible without Macros because your idea suggests
conditional cell selection. It makes perfect sense, however it requires
some more logic than Excel will allow in formulas.

I'm not sure how to link a cell to a macro, I always use commandbuttons or
hotkey combinations to fire macros. If your description of your sheet was
accurate, this macro will give you your last 1000 average in a message box,
it could put it in a cell, but it wouldn't update automatically. You could
have the macro fire every time the sheet was changed I suppose.

Sub AvgLast1000()
Dim BrokenTotal, Count, RunningTotal As Integer

Count = 0
MsgBox "Row: " & ActiveCell.Row & ""

While RunningTotal < 1000 And ActiveCell.Row - Count 0
If ActiveSheet.Cells(ActiveCell.Row - Count, "A").Value 0 Then
BrokenTotal = BrokenTotal + ActiveSheet.Cells(ActiveCell.Row -
Count, "B").Value
RunningTotal = RunningTotal + ActiveSheet.Cells(ActiveCell.Row -
Count, "A").Value
End If
Count = Count + 1
Wend

If RunningTotal < 0 Then
MsgBox "Broken % for the last " & RunningTotal & " items: " &
BrokenTotal / RunningTotal
Else
MsgBox "Calculation not possible"
End If
End Sub

To add the macro, Tools Macros Visual Basic Editor
Then Insert Module
Then just cut and paste the subroutine.
Close Visual Basic Editor

To run, select a cell anywhere on the row for which you want to calculate
the previous 1000 products' brokenness.

ALT+F8
Run the AvgLast1000 macro.

It could also be linked to a hotkey from the ALT+F8 window under Options...

Then again, maybe you're not interested in using macros. ^.^

Thanks,
Tangent



"americanenergy"
<americanenergy.21sicz_1137519907.4604@excelforu m-nospam.com wrote in
message news:americanenergy.21sicz_1137519907.4604@excelfo rum-nospam.com...

Hi. I have a need for a formula that has me stumped. The first Colum is
user entry of products used per day with a entry total in row 33 from
the sum of 2 to 32 in column a. The second column is user entry for
broken (unusable product) for that day with a total in row 33 from the
sum of 2 to 32 in column b. The third column is the percentage of
unusable product verses total product per user entry (Example B2/A2)
with a total average percentage of broken product to usable product in
row 33 from the average of column c rows 2 thru 32. This was very
simple but now I need a cell to provide me with the percentage of broke
products for the last 1000 entries, not using any of the entered
information above 1000 products used.Any help would be much
appreciated. I hope you understand my gibberish.So what I am looking
for is no mater the QTY user entered I will get the % of broken product
for only the last 1000 products entered or up to the first 1000 products
entered in column A..Respectfully Tom


--
americanenergy
------------------------------------------------------------------------
americanenergy's Profile:
http://www.excelforum.com/member.php...o&userid=30564
View this thread: http://www.excelforum.com/showthread...hreadid=502130