0

enter image description here

What I want to achieve is, to create a universal formula for Bob, George, Charles, to calculate their balance based on "Affects balance" field.

So in the example below results would be following:

C3: 400, D3: 150, E3: 240, F3: 10 //(Because in/out field affects only Bob's balance)
C4: 280, D4: 90, E3: 240, F3: -50 //(Because in/out field affectS Bob's and Charles's balance equally)

Please take a look at this example sheets:

https://docs.google.com/spreadsheets/d/1NggjOiebkfUguugtw6lrzBy11F5fVJdree_WG0H3jVA/edit#gid=0

I can't figure out how would be the formula. Any suggestions?

1
  • This question is insufficiently specific. I don't know how @basic figured out what you wanted without your saying it, but please edit your question to include details on how this is supposed to work.
    – Alex M
    Apr 27, 2020 at 5:02

1 Answer 1

0

Try following formula:

=D2+IFERROR((SEARCH(D$1,$B3)>0)*($A3/(LEN($B3)-LEN(SUBSTITUTE($B3,",",""))+1)),0)

enter image description here

1
  • OH! The delta is split across however many users are in the "affects" field for that change record. I didn't understand until I saw your answer. Good job. I've deleted mine.
    – Alex M
    Apr 27, 2020 at 5:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .