Testing if a condition = TRUE

[Lestat]

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,

 

[Mackers]

So the running total sales account should have a formula =MIN(15000, J2+E3), where J2 is the initial figure of 15,000, J3 is the cell you are entering the formula into and E3 is the sales figure (so -191 or 2520). This will keep a running total but not go above 15000.

The sales rep commissions in G3 should thus read =MAX(E3+J2-15000, 0)/2), where E3 is the sales figure, J2 is the previous sales account.

Edit: I may have got the columns confused as I had a bit of difficulty understanding exactly what you wanted, so if you need J to remain as the running total without the 15000 limit then you will need to use a different column and simply exchange all of the J formulas for, say, K formulas. Let me know if you need any of this explaining or if it doesn't work.

Hope that helps

Mackers

 

[Lestat]

If someone can just tell me what's wrong with this formula, I think I figured it out...

=IF((AND(G3>=0,E4>=0)),E4/2,0),IF((AND(G3>=0,E4>=0)),(G3+E4)/2,0)

I'm getting a #VALUE! error. The value in cell G3 is: -191 and the value in cell E4 is: 2520.

What's wrong with the formula?

 

[Mackers]

Hi Lestat

Did you try my solution?

Your formula has some issues with brackets, for sure. I'll try to highlight to make it clear:

=IF((AND(G3>=0,E4>=0)),E4/2,0),IF((AND(G3>=0,E4>=0)),(G3+E4)/2,0)


Your first IF() closes where the red bracket closes, which is where the statement finishes. Additionally you have some superfluous brackets before each of your AND() functions.

Hope that helps

Mackers

 

[igold]

There are too many parenthesis's and an extra zero... Your first IF is the AND Statement, if that evaluates to true then E4/2, if not true you wrote it should be zero, but that should end the statement. You put in another comma and I believe you wanted the second IF to be evaluated if the the first IF was false. Does this work for you...

=IF(AND(G3>=0,E4>=0),E4/2,IF(AND(G3>=0,E4>=0),(G3+E4)/2,0))

igold

 

[Lestat]

Thank you Mackers. I really appreciate you jumping into my mess and trying to help find a solution. It's my fault for making it more complicated than it is. It's really pretty simple....

First, there is no max or ceiling value for the sales account. It just starts at $15000. The floor however would be zero, but that's moot, because the sales rep would no longer be with us well before then.

So:

Sale #1 results in a $1000 loss.
Sales Account = $14000
Commissions = $0

Sale #2 results in a $1500 profit.
Sales Account = $15250
Commissions = $250

And so on... Do you see what I'm looking to do now? Commission is 50% of the $1500, but only $250 is paid, because the Sales account was only at $14000. Once the $15000 is reached, then we can split the remaining commissions.

Sale #3 results in a $2000 profit.
Sales Account = $16250
Commissions = $1000



Ideally, I'd like to keep a running total only on the sales account. I can total the commissions and sales separately.

 

[Lestat]

Thanks Mackers,

I looked up how to use an AND statement and the example had: =IF((AND(c2>20,D2>=30)),"pass","fail"). In this case, the first and last brackets close the IF statement, the 2nd and 5th close the AND statement, and I assume the 3rd and 4th close the condition of the AND statement?

I might need to take a logic course before I improve at Excel -lol.

Thanks again!

 

[Lestat]

Thank you. You guys are awesome!

 

[Lestat]

There are too many parenthesis's and an extra zero... Your first IF is the AND Statement, if that evaluates to true then E4/2, if not true you wrote it should be zero, but that should end the statement. You put in another comma and I believe you wanted the second IF to be evaluated if the the first IF was false. Does this work for you...

=IF(AND(G3>=0,E4>=0),E4/2,IF(AND(G3>=0,E4>=0),(G3+E4)/2,0))

igold

Unfortunately, this doesn't work. Why am I being so stupid?!?! It seems simple enough...

Let me try and work out all the conditions...

If G2 is GREATER than 0, AND E3 is GREATER than 0, then I simply need to return E3/2 in G3.

But if G2 is LESS than 0, AND E3 is GREATER than 0, then I need to return the sum of G2 + E3 divided by 2 in G3. UNLESS...

If E3 is greater than 0, but G2 + E3 is still less than zero. Then I need to again return 0 in G3. So...

| J2 = 15000
E3 = 1000 | G3 = 500 | J3 = 15500 (50% of E3 gets added to both G3 and J3)
E4 = -2000| G4 = 0 | J4 = 13500 (because this brings the J column less than 15000, nothing is paid)
E5 = 2000 | G5 = 250 | J5 = 15250 (the first $1500 of E5 gets added to J5, because J5 is less than 15000. Then 50% of the remaining 500 gets added to both G5 and J5).

I hope that makes sense.

 

[igold]

If someone can just tell me what's wrong with this formula, I think I figured it out...

=IF((AND(G3>=0,E4>=0)),E4/2,0),IF((AND(G3>=0,E4>=0)),(G3+E4)/2,0)

I'm getting a #VALUE! error. The value in cell G3 is: -191 and the value in cell E4 is: 2520.

What's wrong with the formula?

Hi Lestat,

I did not read your initial post when I tried to help you with the IF statement. That being said when I create a Nested IF Statement with the logic you are using in Post #9, then you are short one condition on your last IF. Namely, what happens If E3>0 AND G2 + E3 < 0 is false.

So far I have this:

=IF(AND(G2>0,E3>0),E3/2,IF(AND(G2<0,E3>0),(G2+E3)/2,IF(AND(E3>0,(G2+E3)<0),0,"What about False")))

igold