View Full Version : [SOLVED:] Formula assistance required.
LutonBarry
07-28-2020, 11:35 AM
I'm hoping someone with a logical brain can help me.
I have a spreadsheet that measures daily performance for different operations. I would like the overall performance for all the separate operations the preceding day to be the target for the operations the following day. I thought I'd come up with a formula but I've noticed it doesn't deliver what I require.
I've attached a spreadsheet with a model of the information with a formula in column F that I thought would do the trick.
The information I would want to see returned by a formula is displayed in column G.
Any ideas folks would be appreciated.
That makes no sense. 83 = 100, 91 = 90, and 81 = 80???
Paul_Hossler
08-04-2020, 06:30 AM
The information I would want to see returned by a formula is displayed in column G.
Any ideas folks would be appreciated.
The best idea I have would be for you to FULLY explain the magic behind column G
Does 'Date' matter?
Does 'Home' matter?
Does 'Full' matter?
What numbers are being percentaged, and against what other numbers?
e.g. Col G= (Sum of 'Full' = Y and Date = Col A) / (Total of Full Count for that line to date)
LutonBarry
08-04-2020, 05:57 PM
Paul and Sam,
Thanks for your replies and apologies as I was not clear.
I have uploaded a revised spreadsheet that I hope is clearer.
What I have is a spreadsheet that records if an item in col B is full or not, these items occur throughout the day with the earliest listed first. The number daily items in Col B may change so will not always be 5 separate items there could be 3 or 7 for example.
What I would like is the previous days final Rolling % result to be the target for the following days items in Col B.
The problem I have encountered is that as I enter the results take Tue 2/Jun/20 for example as they occur throughout the day, the Rolling % is updated accordingly.
What I think I may now need is an additional column with a Target % and a formula that checks the date of the lines above until it encounters an earlier date and takes that occurrence's Rolling % as the target.
Paul_Hossler
08-15-2020, 05:42 PM
A VBA user defined function in column G would do it, but not sure how to make a worksheet formula do it
Option Explicit
Function Target() As Variant
Target = Application.Caller.Offset(0, 1).End(xlUp).Value
End Function
Bob Phillips
08-16-2020, 02:50 PM
This array formula also does the same
=INDEX($H$1:$H7,MAX(IF($H$2:$H7="",0,ROW($H$2:$H7))))
LutonBarry
09-02-2020, 12:54 PM
Thanks Paul and Bob and apologies for the late response I have a very poorly mother at the moment.
You have given me a couple of ideas to work and and for that I am very grateful.
Thanks once again.
Barry
Bob Phillips
09-03-2020, 03:28 AM
No need to apologise Barry. Best wishes for your mother's recovery.
p45cal
09-03-2020, 03:34 AM
In cell G8:
=SUMPRODUCT(($A$2:$A$31<$A8)*($C$2:$C$31="Y"))/COUNTIF($A$2:$A$31,"<" & $A8)
Depending on your version of Excel, you may have to commit this formula to the sheet using Ctrl+Shift+Enter rather than the more usual Enter.
Copy down.
Note that the formula only refers to columns A and C, and doesn't need any helper columns.
edit post posting: Oops, didn't notice Bob's response before I posted.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.