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.
