Need formula & function for the question below

Neeraj Chavan

If I apply for PTO, I want to make sure it's getting subtracted from the "WR Balance Before Application" and it would be straight subtraction selecting cells & it's sorted. The main formula I am looking for is, If I apply again, my "WR Balance Before Application" should be the latest "WR Balance Left After Application" from my last entry. How can I do that? Immediate help would be a great help and appreciated.

PCG Sam Harwart

Hi @Neeraj Chavan ,

Perhaps you could have [WR Balance Before Application] be the total WR balance less any [Approved in HRMS] and the [WR Balance Left After Application] could be the total WR balance before application less any pending requests. Where does the WR balance come from?

1. [WR Balance Before Application]: =[WR Balance]@row - SUMIF([Approved in HRMS]:[Approved in HRMS], 1, [WR Applied]:[WR Applied])
2. [WR Balance Left After Application]: =[WR Balance Before Application]@row - SUMIF([Approved in HRMS]:[Approved in HRMS], 0, [WR Applied]:[WR Applied])

What formulas are you using in there now?

Cheers,
Sam

-
primeconsulting.com

Brian_Richardson

If I'm understanding this correctly, you expect multiple rows for one person? So in the example you may have 1 row for Agrima with WR Balance Before Application = 10 and WR Balance After Application = 9.5 then a second row further down where you want Agrima to show WR Balance Before Application = 9.5 (balance from earlier row) and then subtract a value from that?

I'll show how to do this below, but I'd actually suggest restructuring to make this simpler. If you have 1 sheet with all the names on it, and a separate sheet with the actual PTO changes (adds/removes) on it, then it becomes simple to have a cross-sheet reference formula that simply adds the value for each person.

= SUMIF({Name} , Name@row , {WR Applied} )

If instead you want to build a total down the sheet, you can do this using a couple of helper columns.

Setup an AutoNumber column called "Auto"

Setup a Text/Number column called "WR Applied to Date" . In this column add the following column formula:

=SUMIFS([WR Applied]:[WR Applied], Auto:Auto, <=Auto@row, Name:Name, Name@row)

This will add up a total WR applied, up to and including that row, but not the rows further down. You can then subtract that total applied from your balance.