• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problems forumulating IF statements when i have three possible results to return

N

Nikita

New Member
Hello --

I hope this is not too basic of a question, but I'm struggling to figure out how to create the proper statement to return the result I need.

I want to create a formula that calculates the taxable wage base for each employee each quarter, where the cap on the taxable salary is $7,000. Sample data can be found here:

https://drive.google.com/file/d/0B9kJC7ZbH4YxZFBNeVFjdVUtSHc/edit?usp=sharing

For example, in Q1 employee A is paid $3,000 then the taxable wage base is $3,000. In Q2 employee A is paid $5,000, then the taxable wage base is $4,000 ($7000-$3000 paid in Q1). Then in Q3 employee A is paid $2k and the taxable wage base is $0 because she's already reached the cap.

In the first Q the formula is straightforward: =IF(C3<$C$8,C3,$C$8)

For the 2nd & 3rd Q i used the formula =IF(D3<$C$8, ($C$8-D3), (0)) which works fine if either the person has been paid more than necessary to reach the cap or if the cap was reached in Q1. However, It does not work if the employee was paid less than necessary to reach the cap that quarter. In that instance it returns the total amount necessary to reach the cap. What I would like it to return is a value equal to the pay that quarter. In the sample data, this is highlighted in the pink cell.

Is there a formula that will return '0' when the cap was reached in a prior quarter, or the amount necessary to reach the cap which is either the amount paid that quarter OR the wage cap - the amounts paid in prior quarters.

I hope this makes sense. Thanks in advance for any help.

Nikki
 
Hi Nikki,
Try this in D3 and copy down, check if it works for you or not?

=IF(C3=2000,0,IF(C3=5000,4000,IF(C3=3000,3000,IF(C3<$C$8,C3,$C$8))))

Regards,
 
Thank you for taking the time to respond. Unfortunately, this answer did not provide the correct the response in all of the various circumstances. However, it did help me see how i could structure something using nested IF statements that I must have been missing because I was able to come up with this:

If I use this formula in column E, where the I was having the problem, it returns the right answers each time.
=IF(D3>$C$56,0,IF(($C$56-D3)>E3,E3,($C$56-D3)))

Thanks for the inspiration.
 
Hi,

If we use your below formula in Col E
=IF(D3>$C$56,0,IF(($C$56-D3)>E3,E3,($C$56-D3)))

it will surely give circular ref... if you enter say 5000 in C56 (which is greater than D3)

What is C56 in your formula?

Can you clarify more about your requirement ?
What result you want
 
Sorry. I should have adjusted the formula to match the sample data spreadsheet, which I didn't. C56 should be C8. So the formula is

=IF(D3>$C$8,0,IF(($C$8-D3)>E3,E3,($C$8-D3)))

This returns all of the results I need: If the $7k wage limit was already met, it returns '0'; if the amount necessary to meet the wage limit is more than the amount paid in the quarter, it returns the total paid; and if the amount necessary to meet the wage limit is less than paid, it returns the appropriate amount.

Thanks for the help.
 
You must log in or register to reply here.
Back
Top