Need help w/Value if True and Value if False

[Will D]

I need F2 to be 70% of E25, but if that calculation is greater than $2500, I need F2 to be $2500. And if that calculation is less than $2500, I need F2 to be the same value as E25.

And then I need F3 to display the amount that F2 was over $2500, if it was over $2500. If it wasn't, it can display $0.

Is this possible?

[dogberry]

Hi

Always best to post a sample worksheet that way whoever solves it for you will be able to have the correct cell references etc

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.



Chris

[kbkumar]

Hi Will D,

Use this in cell F2:

Please Login or Register  to view this content.

Hope this helps.

[rcm]

isn't there a contradiction? f2=.7*e25 but yet you will set f2=e25 if f2<2500

[Ace_XL]

in F2
=if(min(2500,e25*0.7)<2500,E25,e25*0.7)

In F3
=max(0,(E25*0.7)-2500)

[Will D]

in F2
=if(min(2500,e25*0.7)<2500,E25,e25*0.7)

In F3
=max(0,(E25*0.7)-2500)

Thanks. I tried your formulas and it didn't seem to work. E25 is $4,721.97, and after plugging in your formulas, I get $3,305.38 in F2 and $805.38 in F3. Thoughts?

[Will D]

Hi Will D,

Use this in cell F2:

Please Login or Register  to view this content.

Hope this helps.

Thanks. I tried yours and while it seems to work when 70% of E25 is greater than $2,500, it doesn't seem to work when E25 is less than $2,500. I just realized that I did not do a good job in communicating my original post.

F2 needs to be 70% of E25, up to $2500. And if 70% of E25 is less than $2500, F2 should be that number which comes from 70% of E25, not just a mirror image of E25.

[Will D]

So I tweaked kbkumar's F2 formula to =IF((E25*70%)>2500,2500,(E25*70%)) and it seems to work.
And Ace_XL's F3 formula of =MAX(0,(E25*0.7)-2500) also seems to work.

Can anyone verify this?

[kbkumar]

F2 needs to be 70% of E25, up to $2500. And if 70% of E25 is less than $2500, F2 should be that number which comes from 70% of E25, not just a mirror image of E25.

The formulas you mentioned in post #8 must do this now.

Earlier, in the OP it seems you had mentioned "And if that calculation is less than $2500, I need F2 to be the same value as E25", hence the formula was constructed accordingly.

[Will D]

Well, I'm an idiot. F3 can only go up to $2500. Anyone know how to tweak F3's formula to accommodate? I attached the spreadsheet.

[rcm]

Here you go.
F3 will take the excess of e25*70% up to 2500
F4 will take any excess of 5000

F6 is the checkpoint to ensure that e25 was distributed 100% among F2,F3,F4