# piecewise function

1. ## piecewise function

Hi

I need help with a piecewise function in excel using =IF() statements

the value is 0 if x<=25, 25 if 25<x<=500, 50 if 50<x<=100, and 100 if x>100

The trick is that each value may only be given out once per set of X's and must stack if not present prior to that value

if you have x = 26,27,101, the values should be 25,0,150
or x = 15,16,101 the values would be 0,0,175

Any ideas on how to incorporate that into an IF statement?

I have excel 2007, not 2003

This is what I have so far, but it doesn't incorporate the fact that you can't have more than one 25 used, one 50 used, and one 100 used:

=IF(C4<=25,0,IF(C4>100,100,IF(C4<=500000000,25,IF(C4<=100,50)))

Thanks!

2. ## re: piecewise function

Just tried another function and it still isn't working. I'd really appreciate help, sorry for taking up your time

3. ## re: piecewise function

Post a workbook with examples, and please change your profile to Excel 2007.

4. ## re: piecewise function

I'm not able to due to policies. I am able to make up numbers, similar to what I did in my first post.

You have net values of 0, 24,45, and 110

You get a bonus each time a new goal is met, these goals are at/above 25 (you get \$25), at/above 50 (you get \$50), and at/above 100 (you get \$100). If you get 101, you will receive 100+25+50 = \$175. However, if a previous net value already met the goal of above 25, you do not get the initial \$25 again, you'd merely get 100+50=\$150. If a previous net value already met the goal above 50, you do not get the initial \$25 or \$50 at this goal, you only get \$100.

And I changed my profile to excel 2007. Sorry about that, I had 2007 set when i made this account, but there was an error somewhere and I didn't change it from default the second time.

5. ## re: piecewise function

Is there any more detail required?

6. ## re: piecewise function

Your post does not comply with Rule 10 of our Forum RULES. Please acknowledge the response you receive, good or bad. If your problem is solved, please say so clearly.

Edited this time

7. ## Re: piecewise function

Originally Posted by royUK
Your post does not comply with Rule 10 of our Forum RULES. Please acknowledge the response you receive, good or bad. If your problem is solved, please say so clearly.

Edited this time
I apologize if I am misunderstanding your post...

I did in fact acknowledge his post on my thread... right in the post after. I am not legally able to post exact data, so I instead posted hypothetical data. I shouldn't have double posted, that was my fault.

So no, his post did not solve my query.

8. ## Re: piecewise function

If you can't post a workbook from work, post one from home. All someone needs is one or more examples with a clear explanation -- no names, social security numbers, bank PINs, mothers' maiden names, ...

9. ## Re: piecewise function

Okay, I added an example of the workbook.

I'm looking for a function that can do that calculation automatically, similar to what I posted up top.

To abide by rule 10... no your post has not completed my query.

10. ## Re: piecewise function

I dont follow the logic on your formula. Why dont you take 3% for the first 50, 5% for the next 50 and then 10% on the remaining 20?

11. ## Re: piecewise function

Think of it like you're a book author and your book is being published with different landmarks for varying percentage returns.

If you sell less than \$25 total for the books, you don't get any percentage back. If you sell \$30 worth of books, you get 3% of that \$30 back. However, in the future you will not get any additional money back until you reach the next landmark which in this case is over \$50. If you sell \$55 worth of books, you only get 5% of \$5, rather than getting both 3% of \$25 (because this landmark has already been reached) and 5% of \$5.

Take for example: If you sell \$25 worth, then \$50 worth, then \$120 worth

When you are getting money back for the \$120 worth, the ONLY benefit you receive is the 10% on \$20. You don't receive the 3% or the 5% because you ALREADY got those in earlier book sales.

Do you understand what I mean?

The posts above have not completed my query

I have the equation I need to add up the numbers correctly, I just don't have the right equation that removes percentages that have been used in earlier calculations

12. ## Re: piecewise function

The posts above have not completed my query
I trust you to tell us when we're done

I'm still befuddled. Can you show an example for the same rate structure where the net values are

0 20 30 60 90 100 120

... with the calculations as you showed in the last example?

13. ## Re: piecewise function

Here are the calculations

The 30 value gives a return of 3% on 5 (30-25=5). The 3% return has now been used, it can not be used again. The 60 net value would normally get 3% of 25 (50-25=25), but the 3% has again already been used. The 60 net value gets a 5% return on 10 (60-50=10). The net values of 90 and 100 get no return because both the 3% and the 5% return percentages have been used. The 120 only gets a 10% return on 20 (120-100=20) because the 3% and 5% have already been used.

The above post has not answered my query.

Shg: The only reason I am saying the above is because royUK reprimanded me over not specifically noting that my question wasn't answered completely

14. ## Re: piecewise function

Try this,

``Please Login or Register  to view this content.``
committed with Ctrl+Shift+Enter

HTH,
windknife

16. ## Re: piecewise function

DonkeyOte: I was unaware that it is bad form to post the same question on two separate websites, with the only goal to receive an answer as quickly as possible. I don't understand why you felt the need to point it out, but I digress.

Windknife: The function you posted works about 75% of what I need. The additional percentages on the next higher threshold do not stack if they haven't been used previously. Any idea how I can fix this?

(the above posts have not yet completed my query, however there is a possibility that windknife's post has completed my query)

17. ## Re: piecewise function

Your thread is being closed for non compliance with our rules. Please PM me when you have read the forum rules RULES and made the changes to your post accordingly, i will then unlock your post so you may continue with the thread!

Post Closed

You would have known about cross posting if you had bothered to read the Forum Rules that you agreed to when you joined

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1