Creating Running Balances using the OFFSET Function

Question

I have three balances:

Credit Limit (C8)

Office Supplies (F8)

Training (I8)

In Column D, I have my drop-down menu (Credit Limit, Office Supplies, Training)

In Column H, I have my additional fund amounts

My problem:

if D15=“credit limit”, I want to use this formula: =if(d15=“Credit Limit”, sum(h15,-g15, offset(c9,-1,0)),

if D15=“office supplies”, I want to use this formula: =if(d15=“office supplies”, sum(h15,-g15, offset(f9,-1,0)),

if D15=“training”, I want to use this formula: =if(d15=“training, sum(h15,-g15, offset(g9,-1,0)),

I know this will be a nested statement using the if function. I keep getting a circular error.

Answer 1

Have you tried it? Feel free to share any update.

Answer 2

Hi,

Share some data in a format that can be pasted in an MS Excel file and show the expected result.

Answer 3

=IFS(D15="Credit Limit",SUM(H15,-G15,OFFSET(C9,-1,0)),D15="office supplies",SUM(H15,-G15,OFFSET(F9,-1,0)),D15="training",SUM(H15,-G15,OFFSET(G9,-1,0)))