I'm a fairly new Excel user and need help with a formula.
I know the formula below is wrong. It's just my first attempt!!
What I need is a formula that does the following: If 195.00 (this value is in C5) minus the amount in D5 (value only) equals any amount up to 195.00, then subtract the value in E5 up to 195.00 and put this number in F5.
=If(C5-D5=<195,subtract E5 up to 195)
Let me know if you need more clarification and thanks in advance.
KFain
Last edited by Kfain; 05-31-2012 at 09:32 AM.
Could you give a little more clarification, Maybe an example or a sample workbook.
It's unclear what you want to subtract E5 from.. but here's a guess, the important part being MIN(E5,195) so that 195 is not exceeded:
=If(C5-D5=<195,C5-D5-MIN(E5,195))
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Originally Posted by NBVC
--
E5 is subtracted from 195 minus D5
Does this help? We're close.
So do you mean?
=If(C5-D5=<195,195-MIN(195,E5)-D5)
You can play around with the logic...
I realize it is easier to see exactly the data I'm working with. I've attached a test dummy of the spreadsheet.
Thanks NBVC for your help so far. I may be doing something wrong in entering your formula.
Please let me know if you have any questions.
Thanks!!! Kim
Maybe it's simpler than I am making it out to be.. but I am still confused as to what you actually need...
What are your expected results in the sheet you attached?
Basically:
C minus D
If the answer to this is anything over zero and there is any amount in column E subtract E from C minus D but only up to the total of C (i.e. 195 minus a 50 dollar payment.) Then column E has 300.00, so only take the amount that adds up to 195, so 145, leaving 155.00 in column G and zero in column
Does this make sense at all? Sorry. I'm very new at doing formulas. This is probably so easy, it's making you laugh!!
Thanks, Kim
Another guess:
=MAX(0,E5-(C5-D5))
Not quite. I'made a TEST2 file and have put in the correct values in Column G, which is where I need the formula. Maybe this will make more sense.
Notice family H has 2 kids in band, so their total in C12 is twice everyone else's. The Kroger Balance number is a fund raising number they have that will be deducted from their balance owed.
Thanks again. Let me know if something is not clear.
Kim
Just using this formula in G:
=MIN(E5,F5)
gives same results except for the Family h... which I am not sure why exactly you expect $0 there? Unless having multiple siblings makes you immune to the Kroger cost.. then =IF(C5>195,0,MIN(E5,F5))
That's it!!!! NO, that was my mistake!! So, basically, what does MIN(E5,F5) tell the program to do? So, maybe I'll know what to do next time!
Thanks so much,
Kim
It just takes the minimum of the 2 cells.
Man. I feel really dumb!! Made something easy SOOO difficult. Lots to learn here! thanks again!! Have a great day!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks