Hi guys,
i want to make A SIMPLE STOCK SHEET, i just want a formula that can show blank cell, or maybe "0" when there is no entry for that day. I have attached a file for reference. Please help me T___T
THANK YOU IN ADVANCE
Hi guys,
i want to make A SIMPLE STOCK SHEET, i just want a formula that can show blank cell, or maybe "0" when there is no entry for that day. I have attached a file for reference. Please help me T___T
THANK YOU IN ADVANCE
In Q10 copied down:
=IF(O10&P10="",0,IF(ISNUMBER(LOOKUP(2,1/(Q$9:Q9<>0),Q$9:Q9)),LOOKUP(2,1/(Q$9:Q9<>0),Q$9:Q9),0)-IF(P10="",0,P10)+IF(O10="",0,O10))
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Or this slightly shorter one:
=IF(COUNT(O10:P10)=0,0,SUM(O$10:O10)-SUM(P$10:P10))
BSB
In Q10
Then copy down.Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
thank you for all your replies, thank you to all you amazing people!
This one does NOT produce what you want (or showed that you wanted) in row 17 - it produces -500 where you said you wanted 4700. Mine and BSB's both do exactly what you said you wanted.
Excel 2016 (Windows) 32 bit
N O P Q R S T 9Date IN OUT BAL. AliGW BSB kvsrinivasamurthy 10 1 5000 300 4700 4700 4700 4700 11 2 400 4300 4300 4300 4300 12 3 200 300 4200 4200 4200 4200 13 4 0 0 14 5 500 4700 4700 4700 4700 15 6 500 5200 5200 5200 5200 16 7 0 0 0 17 8 500 4700 4700 4700 -500 18 9 500 500 4700 4700 4700 4700 19 10 0 0 20 11 0 0 21 12 0 0 22 13 0 0 23 14 0 0 24 15 0 0 25 16 0 0 26 17 0 0 27 18 0 0
Sheet: Sheet1
If you want blanks instead of 0s, change mine to this:
=IF(O10&P10="","",IF(ISNUMBER(LOOKUP(2,1/(Q$9:Q9<>0),Q$9:Q9)),LOOKUP(2,1/(Q$9:Q9<>0),Q$9:Q9),0)-IF(P10="",0,P10)+IF(O10="",0,O10))
Excel 2016 (Windows) 32 bit
N O P Q R 9Date IN OUT BAL. AliGW 10 1 5000 300 4700 4700 11 2 400 4300 4300 12 3 200 300 4200 4200 13 4 14 5 500 4700 4700 15 6 500 5200 5200 16 7 0 17 8 500 4700 4700 18 9 500 500 4700 4700 19 10 20 11 21 12 22 13 23 14 24 15 25 16 26 17 27 18
Sheet: Sheet1
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Last edited by AliGW; 04-16-2019 at 05:09 AM.
@AliGW
Pl see The file I have attached. Function is working ok.
Oh, I see - it's two formulae! Well, you don't need two - you can do it in one with my solution.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks