Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have a value in cel A2 say 10,000 , & in cell B2 to F2 i have months & some values getting a total of 13000 (B2 to F2). I need a forrmula to know the difference between A2 & (B2 to F2) should not go less than zero.. B2 is 2000 ; C2 is 4000 ; D2 is 2000 ; E2 is 4000 ; F2 is 1000 (13000 in total). If i see the difference its -3000. But i want a formula that stops by not goin to negative (columns getting added from back) in sense if i look to the last 3 columns its 7000 & last 4 columns is 11000 (whixh is higher that A2) ... so i need the diff of 3000 as out put (A2 - sum(D2:F2) is it possible
Solved by C. C. in 34 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 11/07/2018 - 12:38
Welcome, thank you for choosing Got It Pro-Excel! I'll be glad to assist you with your concern. :)
User 11/07/2018 - 12:39
hi
User 11/07/2018 - 12:39
I have a value in cel A2 say 10,000 , & in cell B2 to F2 i have months & some values getting a total of 13000 (B2 to F2). I need a forrmula to know the difference between A2 & (B2 to F2) should not go less than zero.. B2 is 2000 ; C2 is 4000 ; D2 is 2000 ; E2 is 4000 ; F2 is 1000 (13000 in total). If i see the difference its -3000. But i want a formula that stops by not goin to negative (columns getting added from back) in sense if i look to the last 3 columns its 7000 & last 4 columns is 11000 (whixh is higher that A2) ... so i need the diff of 3000 as out put (A2 - sum(D2:F2) is it possible
Excelchat Expert 11/07/2018 - 12:39
I've read your concern, and I believe this is doable by making use of an IF statement. Will it be okay if you can provide the data you're working on, or if you can create a sample data on the document preview. :)
User 11/07/2018 - 12:40
sure...
Excelchat Expert 11/07/2018 - 12:43
Okay, nice. So basically, what you want to do is to get the difference between A5 and values starting at F5 going to B5. You want to stop deducting these values if the next one would create a negative number. In your example, you want to stop deducting when it reaches D5, and thus return a value of 3000 only.
User 11/07/2018 - 12:43
exactly
Excelchat Expert 11/07/2018 - 12:44
Okay, noted on that. Let me work on it for a while and update you the soonest. :)
User 11/07/2018 - 12:45
ok awaiting for an awesome response
Excelchat Expert 11/07/2018 - 12:49
Okay, already done. If you look at B7, the formula contains a combination of IF statements to check on what value will the deduction return a zero, and will stop deducting from there. :)
User 11/07/2018 - 12:50
actually ur formula bar is not visible to me
Excelchat Expert 11/07/2018 - 12:50
Oh, you can click on cell B5 in the document preview to look at the formula.
User 11/07/2018 - 12:50
can u send off the formula in chat box
Excelchat Expert 11/07/2018 - 12:50
Sure thing, here it is:
Excelchat Expert 11/07/2018 - 12:50
=A5-IF(A5-F5<0,0,F5)-IF(A5-sum(E5:F5)<0,0,E5)-IF(A5-sum(D5:F5)<0,0,D5)-IF(A5-sum(C5:F5)<0,0,C5)-IF(A5-sum(B5:F5)<0,0,B5)
Excelchat Expert 11/07/2018 - 12:51
You can test this out by changing values on the row 5. :)
User 11/07/2018 - 12:51
awesomem.. this was for a small range....
User 11/07/2018 - 12:51
but if i need to go for a longer range?
Excelchat Expert 11/07/2018 - 12:52
If that would be the case, we may need to employ a helper column to avoid creating a longer multiple IF function.
User 11/07/2018 - 12:52
& how is tat?
Excelchat Expert 11/07/2018 - 12:53
Okay, let me demonstrate it to you by expanding our example.
User 11/07/2018 - 12:53
sure...
Excelchat Expert 11/07/2018 - 12:56
Already done! Row 6 is our helper while cell B8 would contain the formula.
Excelchat Expert 11/07/2018 - 12:57
Row 6 would contain A5 being deducted with sums of values ending in Q5. If you look at the formula, Q5 is made absolute for this to not be changed while dragging to the right.
Excelchat Expert 11/07/2018 - 12:58
Basically, =$A$5-SUM(B$5:$Q$5) is entered in B6 and is dragged until we reach column Q.
User 11/07/2018 - 01:00
in B8, u are using Arrayformula
User 11/07/2018 - 01:00
but this seems not somethin that is available in xl
Excelchat Expert 11/07/2018 - 01:00
Yes, that's true. So basically, you need to press CTRL+SHIFT+ENTER
Excelchat Expert 11/07/2018 - 01:01
In Excel, you also just have to do that.
Excelchat Expert 11/07/2018 - 01:01
=MIN(IF(B6:Q6>0,B6:Q6))
Excelchat Expert 11/07/2018 - 01:01
After you type in that formula, instead of pressing just ENTER, press CTRL+SHIFT+ENTER
Excelchat Expert 11/07/2018 - 01:02
What should be displayed in your function bar is {=MIN(IF(B6:Q6>0,B6:Q6))}
User 11/07/2018 - 01:04
ok...
Excelchat Expert 11/07/2018 - 01:04
Is there anything else that I can assist you regarding the solution provided? :)
User 11/07/2018 - 01:04
& is ther any way that these can be combined into a single line formula
User 11/07/2018 - 01:04
so that i can drag the same for the list
User 11/07/2018 - 01:04
**drag down
Excelchat Expert 11/07/2018 - 01:05
Unfortunately, either we use the formula in B7 or make use of the formula in B8 instead. Instead of creating a helper column, you can actually create a helper sheet for this.
Excelchat Expert 11/07/2018 - 01:06
And in your original worksheet, you can just link in the results from the helper sheet.
Excelchat Expert 11/07/2018 - 01:08
If you look at the document preview, you'll see that I transferred the helper column in another sheet. From there, I've referenced B8 to look into the helper sheet.
Excelchat Expert 11/07/2018 - 01:09
I can set this helper sheet on your working file if you're okay with sharing it. You can delete any confidential info before uploading.
User 11/07/2018 - 01:10
actually i jst started creating a file like this...
User 11/07/2018 - 01:10
so i don have any fiel to shar...
User 11/07/2018 - 01:10
but ur formula has really helped me save a lot of time
Excelchat Expert 11/07/2018 - 01:10
Oh I see. Nice to hear that. :)
Excelchat Expert 11/07/2018 - 01:10
Is there anything else that I can assist you regarding the solution provided? :)
User 11/07/2018 - 01:11
any macros on this case that can help
Excelchat Expert 11/07/2018 - 01:12
Oh, unfortunately, we're restricted from providing macro/VBA-related assistance since this may pose a threat to our customers.
User 11/07/2018 - 01:12
oo no issues...
Excelchat Expert 11/07/2018 - 01:12
Thanks for understanding! Btw, you can download a copy of the document preview by going to File > Download as > Microsoft Excel (.xlsx). The formulas will be downloaded as well. :)
User 11/07/2018 - 01:13
thanks a lot for ur time...
User 11/07/2018 - 01:13
really helpful
Excelchat Expert 11/07/2018 - 01:13
Nice! You can now end the session via the button on your upper right (sorry, can't do it from our end). I would also appreciate it if you can leave a great feedback after this.Thanks so much for using Got it pro and have a nice day! :)
User 11/07/2018 - 01:13
NIce dayto u as well...

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.