PDA

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.

SamT
08-04-2020, 12:51 AM
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.