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!

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

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.

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

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, ...

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?

FWIW, since x-posted:

http://www.mrexcel.com/forum/showthread.php?t=475319

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)

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