conditional result

[edhinfla]

I have a worksheet that I need formula help with. My worksheet is between B6 and K19.
Row F6 through F19 changes dollar amount however if it is OVER 25000.00 I want Column H6 through H19 minus 25000.00 amount to be ADDED into Column K6 through K19.

ie: If F10 is greater than 25000.00 Leave 25000 amount in there and then subtract H10-25000 and put the difference + whatever is in K10 into K10. Do this throughout my rows of F6:F19

Can someone please give me a formula that will solve this problem? I have tried and I can not seem to get it working properly! Thanks!

[mikerickson]

edhinfla,

Welcome to the forum.

Most people who post in this forum "Need Formula Help", so I changed your title to include a desription of what kind of help.

Try putting in K2

=H2-MIN(25000,F2)

Selecting K2:K19 and using Fill Down.

[shg]

Ed, it's forum etiquette that your title explain what you need help with. Yours doesn't.

Go to your post, click Edit, then Go Advanced, then change the title.

Thanks.

[edhinfla]

Sorry about the Title...I am new to the forum and certainally a novice at Excel. Thanks for your comments and advice.

[edhinfla]

edhinfla,

Welcome to the forum.

Most people who post in this forum "Need Formula Help", so I changed your title to include a desription of what kind of help.

Try putting in K2

=H2-MIN(25000,F2)

Selecting K2:K19 and using Fill Down.

No your formula did not work correctly. I am putting in a few columns as an example of what I am doing.


F6 $12,515.33 H6 $20,024.52 K6 $2,500.00
$17,524.52 $28,039.23 $3,039.23
$25,000.00 $40,000.00 $15,000.00
$25,000.00 $40,000.00 $15,000.00
$25,000.00 $40,000.00 $15,000.00

Top Column is F6 and goes to F19, etc H6:H19, & K6:K19.....when values in F column reaches 25000 then put excess into corresponding K column plus what ever else value is in there in K. I manually have done what I expect a formula to do in this example. I am sorry if I was not clearer before.

[shg]

Ed, it's still not clear (to me) what you want.

If you enter some number into a cell, a formula cannot limit the amount (e.g., to 25K) and put the residual in some other cells.

Perhaps you could post a spreadsheet showing desired results?

[edhinfla]

OK about not knowing what I want so I am attaching my worksheet for your review. This is my personal record worksheet I would like to use for investments. The figures are in there as an example so you can see the dynamics. You can see that the Deposit Amt and the Term Amt keep changing however the Deposit Amt only can NEVER exceed $25,000 so all in excess of of that developed by the Term Amt column should go into the Withdraw column in addition to what already may be manually put in there.

The only other thing that does not work right is the dates I enter the date to start and it should expand the dates every 30 days down through the list. It does not work for what I have in there as the year does not change during the 30 day sequence and doesnt start working until I get to the 3rd date. If you can help me I would be most grateful as I have spent hours trying to learn & formulas, etc. Thanks and if you cant help me, thanks for answering my post and trying. God Bless! ....Ed

[shg]

So there must be two types of withdrawals -- those that you do manually, and those that must occur automatically as a result of the deposit otherwise exceeding $25K?

[shg]

Maybe this ...

[edhinfla]

Oh thank you very much...Yes that is exactly what I want it to do ...except for one thing.....When a Withdrawal Amt say in cell G8 ie: $ 100.00 the amount on cell D9 should be decreased by that amount but instead it ADDS to the Deposit Amt instead of subtracting. I would try to change it but those formulas are confusing to me. Thanks sooooo very much what you have done for me SHG . I really appreciate it! .....Ed

[shg]

When a Withdrawal Amt say in cell G8 ie: $ 100.00 the amount on cell D9 should be decreased by that amount but instead it ADDS to the Deposit Amt instead of subtracting.

It does; the withdrawal amounts are negative.

[edhinfla]

Yes, Of course I knew when I wrote the reply that I had already known the answer. Sorry about that! Thanks so much again and I know I have much to learn about Excel. There is a course locally I will take soon. Oh last question: How can I change the darker background into a lighter shade of gray? ..Ed

[shg]

You're welcome.

You can reset the color palette to the Excel defaults (Tools > Options > Colors > Reset), change the style of Normal to eliminate the shading (Format > Style ...), and change the other styles to shades you prefer.