sum less than then... sum more than then...

[Fulsemlan]

Hi there,

I am trying to produce a sheet calculating income from film distribution. But I am stuck. The good thing is that I think you guys can help me very easily.

What I need is a formula to do this:

Take a number from a field (let's say C5), subtract 2 500 000, divide the outcome by 2. Then (the tricky part...) If the outcome is less than zero then it should show 0, if the outcome is more than 9 000 000 then it should show 9 000 000. (The minimum is 0 and the max is 9000 000.)

Any suggestions?

All the best, thanks in advance,

Patrik

[NBVC]

Welcome to the forum.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

PLEASE PM WHEN YOU HAVE DONE THIS AND I WILL DELETE THIS POST

[Andrew-R]

Please Login or Register  to view this content.

[Fulsemlan]

Thank you Andrew but it doesn't seem to work... Hmmm... /F

[NBVC]

How about?

=MIN(MAX(0,(C5-2500000)/2),9000000)

[Andrew-R]

Sorry, slight formula ****-up.

Try:

Please Login or Register  to view this content.

Or go with NBVC's option.

[Fulsemlan]

Hi again. Still no luck here... ;-)

This part works like a charm of course:
=(C5-2500000)/2

Then I would like the minimum to be 0 and the max to be 9 000 000.

/Patrik

[Fulsemlan]

Could it be the 2008 mac version of excel or am I doing a newbie mistake some how?..

[Andrew-R]

I hate to argue with you, but it seems to work fine for me.

What's happening when you try it?

[NBVC]

I changed my formula within less than 1 minute of posting.. do you have the lastest version I posted?

Also,

If I enter 2500000 or less, I get 0,

anything between 2500001 and 20500000 gives me a number between 0 and 9000000...

anything over 20500000 gives me the max 9000000

[Fulsemlan]

Hi again A,

believe me, I trust you! ;-)

What happens? The old: The formula you typed contains an error. Then in the formula builder in one row it says "invalid" where the following "2500000,0,IF(C5>20500000,9000000,(C5-2500000)/2)" is.

Could it be the "," or something? Could it be that my version of excel reads it differently?

/Fulsemlan

[NBVC]

I guess you haven't tested mine then?

[Fulsemlan]

NBVC:

Hi again! When I try your formula it says: Microsoft Excel found an error in the formula you entered. Do you want to accept the correction proposed below?
=MIN(MAX(0,*(C5-2500000)/2)*,9000000)

But the formula doesn't seem to work...

/F

[Andrew-R]

I've never used the Mac version of Excel, so I've got no idea if it treats formula differently.

Does NBVC's version work?

[Fulsemlan]

Hi again both of you! I think I solved it thanks to you!

If I change the "," for a ";" then NBVC's formula works. Will try Andrews soon.

Thanks!

Fu

[Fulsemlan]

Yepp Andrew, with ";" instead of "," your version work also. Thank you!

Mac-related I guess.

Fu

[Andrew-R]

Tell you what, as this seems to be generating more posts than you'd expect from such a straightforward problem, we'll try baby-steps.

Try putting the following formula in an tell us when your Excel gets upset...

=IF(C5<=2500000,0,1)
=IF(C5<=2500000,0,C5/2)
=IF(C5<=2500000,0,(C5/2)-2500000)
=IF(C5<=2500000,0,IF(C5>20500000,9000000,1))
=IF(C5<=2500000,0,IF(C5>20500000,9000000,C5/2))
=IF(C5<=2500000,0,IF(C5>20500000,9000000,(C5/2)-1250000))

Which one of those throws an error?

[Andrew-R]

Just seen your update - glad you got it working.

[romperstomper]

Not Mac related, just regional settings. you could also use:
=MEDIAN(0;(C5-2500000)/2;9000000)