Monitoring funds usage

[Durendal]

Hello everybody,

I am a sales manager. I send funds to my salesman every weeks for them to purchase for me.

What I am trying to do is to monitor the weeks since when they have received funds that they havn’t completely used.
To give you a simple example, what I’d like to do is:

A - In week one, I send him 500$; This same week the salesman bought for 300$. There’s an outstanding of 200$ and total sales of 300$.

B - In week two, I send him 300$; This same week he bought for 100$. There’s an outstanding of 400$ and total sales of 400$.

C - In week three, I send him 300$; This same week he bought for 150$. There’s an outstanding of 550$ and total sales of 550$.

- What I want is a formula that would give me, for A, one week since the funds havn’t been entirely used;

- For B, two weeks since the total sales are 400$ and I gave him 500 in week one;

- For C, two weeks since the first 500$ have been filled, but now I am on the 300$ from the second week.

I hope you can understand what I am asking, don’t hesitate to ask question if you don’t understand. I’ve tried all sort of thing but I’m not able to solve this.

Thanks a lot

 

[Anthony47]

If "Fund Remaining" is calculated in column F then try using in H2 and H3 use in this order:

=IF(F2>0,1,0)&"  Weeks"

=IF(F3="","   ",IF(F3>0,("0"&TRIM(LEFT(H2,3)))+1,0)&"  Weeks")

Then copy H3 down for as lines as you need

 

[Durendal]

Thanks for you answer, I really appreciate you tried to help me. It didn't work though, but I found the solution somewhere else if anyone is interested:


Excel Formula:

=COUNTIF(C$1:C2,">"&E2)

Thanks again!