This has to be ridiculously easy, but my brain can't follow it for some reason.
Column E are entries consisting of both positive and negative numbers. Column G is a running total of column E. Column J needs to be 1/2 of the column E entry, but ONLY if both column E AND column G are positive, AND only up to the difference that Column G is positive. For example:
Entry in E3 is -191. Then column G is -191 and column J is 0.
Entry in E4 is 2520. Then column G should be 2329 (a simple running total of G3+E4). But column J should be 1164.50 (the sum of (2520-191) divided by 2).
I tried this formula in column J: =IF((AND(G4>=0,E4>=0)),E4/2,0). However, this is obviously incorrect, because it's returning 1260, instead of 1164.50.
This formula seems to work: =IF(G3+G4>=0,(G3+E4/2,0). This is fine, however, the following rows in column J are sort of running total, instead of just the number divided by two that I want.
The problem is, I can't seem to figure out how to isolate just the number divided by two, but only if the balance is positive, AND only the amount that the entry from column E is above zero THEN divided by two.
In short, I have column for individual sales (E), sales rep commissions (G), and a sales account starting with $15,000.00 (J). If a sale loses -$191, then the company fronts the loss and the sales account goes to $14,809.00. If the next sale nets $2,520.00 profit, then 50% of it goes to the rep. But they first need to make up for the $191 loss. Then they gets 1/2 of whatever is over the $15,000.00.
So I need E, which is just the sales entry. G, which is a running total of sales. J, which is the sales rep's commission on individual sales. And L, which is a running total of the sales account (starting with $15000).
Again, the problem is in getting the 50% commission figure right. It's not a simple divide by two, because it only gets paid if: The sales doesn't result in a loss, AND if the sales account is greater than $15000. If the account is less than $15000, then either nothing is paid, or only the amount above $15000 divided by two.
I hope that makes sense. This might be more of a math problem than an Excel one and I'd greatly appreciate any help.
Thanks,
Column E are entries consisting of both positive and negative numbers. Column G is a running total of column E. Column J needs to be 1/2 of the column E entry, but ONLY if both column E AND column G are positive, AND only up to the difference that Column G is positive. For example:
Entry in E3 is -191. Then column G is -191 and column J is 0.
Entry in E4 is 2520. Then column G should be 2329 (a simple running total of G3+E4). But column J should be 1164.50 (the sum of (2520-191) divided by 2).
I tried this formula in column J: =IF((AND(G4>=0,E4>=0)),E4/2,0). However, this is obviously incorrect, because it's returning 1260, instead of 1164.50.
This formula seems to work: =IF(G3+G4>=0,(G3+E4/2,0). This is fine, however, the following rows in column J are sort of running total, instead of just the number divided by two that I want.
The problem is, I can't seem to figure out how to isolate just the number divided by two, but only if the balance is positive, AND only the amount that the entry from column E is above zero THEN divided by two.
In short, I have column for individual sales (E), sales rep commissions (G), and a sales account starting with $15,000.00 (J). If a sale loses -$191, then the company fronts the loss and the sales account goes to $14,809.00. If the next sale nets $2,520.00 profit, then 50% of it goes to the rep. But they first need to make up for the $191 loss. Then they gets 1/2 of whatever is over the $15,000.00.
So I need E, which is just the sales entry. G, which is a running total of sales. J, which is the sales rep's commission on individual sales. And L, which is a running total of the sales account (starting with $15000).
Again, the problem is in getting the 50% commission figure right. It's not a simple divide by two, because it only gets paid if: The sales doesn't result in a loss, AND if the sales account is greater than $15000. If the account is less than $15000, then either nothing is paid, or only the amount above $15000 divided by two.
I hope that makes sense. This might be more of a math problem than an Excel one and I'd greatly appreciate any help.
Thanks,
