How do I write the formula to display the first negative in a running total in a new sheet

Question

Widget A 150

Widget A 100

Widget A 23

Widget A -6

Widget A -56

Widget B 200

Widget B 75

Widget B -11

Widget B -88

Widget B -130

I want to display this in a new sheet as:

Widget A -6

Widget B -11

In other words, I want to extract the first negative line of the running total for each widget where Widget A would display -6 and Widget B would display -11. I want this data in a new sheet.

Answer 1

Hello Mike Cardwell,
Thank you for taking the time to share your experience.
On behalf of the Office 365 Support team, I would like to thank you for your time regarding service request #5781357. This case has been associated with another support request, #5781332, which is currently being handled by the appropriate support team.
As a result, this case will be archived and marked as a duplicate to prevent any duplication of effort. All relevant information will remain centralized under the original case, and I remain available should any additional assistance be required.

Thank you for your understanding and cooperation in archiving this duplicate service request.

Kind regards,

Answer 2

To achieve the desired output of displaying the first negative value in a running total for each widget in Excel, you can use a combination of formulas. Here’s a step-by-step approach:

1. Create a Running Total: First, you need to create a running total for each widget. Assuming your data starts from cell A1, you can use the following formula in cell C1 (for running total of Widget A) and drag it down:

      =SUMIF($A$1:A1, "Widget A", $B$1:B1)
   

   For Widget B, you would use:

      =SUMIF($A$1:A1, "Widget B", $B$1:B1)
   

2. Identify the First Negative Value: In a new sheet, you can use the following array formula to find the first negative running total for each widget. For Widget A, you can enter this formula in a cell:

      =INDEX($B$1:$B$10, MATCH(TRUE, $C$1:$C$10 < 0, 0))
   

   For Widget B, you would adjust the formula accordingly:

      =INDEX($B$1:$B$10, MATCH(TRUE, $C$1:$C$10 < 0, 0))
   

3. Display Results: This will give you the first negative running total for each widget in the new sheet.

Make sure to adjust the ranges in the formulas according to your actual data range. This method effectively extracts the first negative running total for each widget as you specified.

---

References:

• Excel statistical functions: GROWTH