calculating stock market bonus shares

[mwvirk]

hello

I am calculating how many bonus shares (Flag marked as B) I have received from the stock market for each of my stocks
and then also checking if I sell some bonus shares then how many are still in my portfolio

in this excel file, I have 2 sheets
sheet1 is for buying, selling and bonus shares received
sheet2 is to check the status that how many bonus shares were received and how many are left. if all are sold then I should get 0 in sheet2 cell D
(but in all cases, I want to keep the actual bonus shares received in sheet2 cell C)

I have multiple issues here in this sheet2:

1- if the cell H in sheet1 is blank then cell D in sheet2 is not counting the total (i should have a value of 0 at least in sheet1 cell H to get the result since the blank cell is not showing the count in sheet2 cell D)
2- if I partially or fully sell bonus shares in sheet1 then it's showing balance zero in all cases in sheet2 cell D (i am checking here to know how many are still in my portfolio)

kindly check and help.

thanks

Test-Copy.xlsx
	A	B	C	D	E	F	G	H
1	Stock name		Flag			Received		Sold
2	Apple		R			100		0
3	Google		R			150		0
4	Intel		R			200		0
5	Facebook		R			250		0
6	Apple		B			10		5
7	Google		B			15		0
8	Intel		B			20		0
9	Facebook		B			25		0
10	Apple		R			200		0
11	Google		R			250		0
12	Intel		R			300		0
13	Facebook		R			350		0
14	Apple		B			15
15	Google		B			20
16	Intel		B			25
17	Facebook		B			30
Sheet1

Test-Copy.xlsx
	A	B	C	D
1	Apple		25	0
2	Google		35	15
3	Intel		45	20
4	Facebook		55	25
Sheet2

Cell Formulas
Range	Formula
C1:C4	C1	=SUMIFS(Sheet1!F:F,Sheet1!A:A,A1,Sheet1!C:C,"B")
D1:D4	D1	=SUMIFS(Sheet1!F:F,Sheet1!A:A,A1,Sheet1!C:C,"B",Sheet1!H:H,0)

 

[mwvirk]

Thank you once again KRice for the detailed explanations and sorry for replying late as I was out of the station.
I tried applying your suggested changes from your last 2 posts and noticed that my desktop is hanging after all the above changes from the previous 2 posts. Also, I got a few errors in a few cells which were working fine before but maybe I have made a wrong change. I am sharing the screenshots and the mini sheet.

I am also thinking of following your suggested format. I am sharing the 3 records in my mini sheet. As you know what are my requirements for the sheet? I wish you can update these 3 records with all the formulas and condition checks and share the Excel file like before and then after the 3 records, I will start inputting the data again.

My total rows for the (only) NA# are 8161 and I have changed the formulas accordingly. The way my computer is hanging, I am afraid if I input the remaining PP/PB/PS/KP recording, my computer might burn or might take ages to input 1 single entry.

One more mistake I made, I didn't take the backup and applied all the changes from the last two posts. So, at this moment, I lost all the previous formulas. I can find all those but will take a lot of time. So, I wish we can fix the current sheet after all the changes:
1- to return the required output
2- minimize the CPU/Memory utilization and work fast. It's a gaming computer and my CPU max goes to 68 even I am playing COD/MOH/CRYSIS games
3- otherwise I wish you can make the changes in 3 records as you know my requirements and I will update the data again. I will listen to your suggestions to remove the merged cells but the output/layout requested by my office is the same as I have designed

Thank you once again and waiting for your helpful reply as usual.

Regards,
MWVirk

Election 2022.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	NA # (2018)	Province	NA # (2013)	Seat Name	Registered Voters	Total Cast Votes	Rejected Votes	Valid Cast Votes	% Of Total Registered Votes	NA Flag	Candidate Name	Party Name	Votes	% Of Candidate Votes	Winner's Name	Winner Party	Runner up Name	Runner up Party
2	NA-001	Kingdom Valley	Blue Area 1	Blue Area 1A	318,150	30,500	500	30,000	9.59%	NA-001	Candidate Name 0001	Party Name AA	1,000	3.33%	#N/A	#N/A	#NUM!	#NUM!
3										NA-001	Candidate Name 0002	Party Name AB	1,000	3.33%
4										NA-001	Candidate Name 0003	Party Name AC	700	2.33%
5										NA-001	Candidate Name 0004	Party Name AD	1,000	3.33%
6										NA-001	Candidate Name 0005	Party Name AE	1,000	3.33%
7										NA-001	Candidate Name 0006	Party Name AF	1,000	3.33%
8										NA-001	Candidate Name 0007	Party Name AG	1,000	3.33%
9										NA-001	Candidate Name 0008	Party Name AH	1,100	3.67%
10										NA-001	Candidate Name 0009	Party Name AI	1,000	3.33%
11										NA-001	Candidate Name 0010	Party Name AJ	1,000	3.33%
12										NA-001	Candidate Name 0011	Party Name AK	1,200	4.00%
13										NA-001	Candidate Name 0012	Party Name AL	1,000	3.33%
14										NA-001	Candidate Name 0013	Party Name AM	1,000	3.33%
15										NA-001	Candidate Name 0014	Party Name AN	1,000	3.33%
16										NA-001	Candidate Name 0015	Party Name AO	1,000	3.33%
17										NA-001	Candidate Name 0016	Party Name AP	1,000	3.33%
18										NA-001	Candidate Name 0017	Party Name AQ	1,000	3.33%
19										NA-001	Candidate Name 0018	Party Name AR	1,000	3.33%
20										NA-001	Candidate Name 0019	Party Name AS	1,000	3.33%
21										NA-001	Candidate Name 0020	Party Name AT	1,000	3.33%
22										NA-001	Candidate Name 0021	Party Name AU	1,000	3.33%
23										NA-001	Candidate Name 0022	Party Name AV	1,000	3.33%
24										NA-001	Candidate Name 0023	Party Name AW	1,000	3.33%
25										NA-001	Candidate Name 0024	Party Name AX	1,000	3.33%
26										NA-001	Candidate Name 0025	Party Name AY	1,000	3.33%
27										NA-001	Candidate Name 0026	Party Name AZ	1,000	3.33%
28										NA-001	Candidate Name 0027	Party Name BA	1,000	3.33%
29										NA-001	Candidate Name 0028	Party Name BB	1,000	3.33%
30										NA-001	Candidate Name 0029	Party Name BC	1,000	3.33%
31										NA-001	Candidate Name 0030	Party Name BD	1,000	3.33%
32	NA-002	Kingdom Valley	Blue Area 2	Blue Area 2B	318,150	63,500	500	63,000	19.96%	NA-002	Candidate Name 0001	Party Name AA	1,000	1.59%	#N/A	#N/A	#NUM!	#NUM!
33										NA-002	Candidate Name 0002	Party Name AB	1,000	1.59%
34										NA-002	Candidate Name 0003	Party Name AC	1,000	1.59%
35										NA-002	Candidate Name 0004	Party Name AD	1,000	1.59%
36										NA-002	Candidate Name 0005	Party Name AE	1,000	1.59%
37										NA-002	Candidate Name 0006	Party Name AF	1,000	1.59%
38										NA-002	Candidate Name 0007	Party Name AG	1,000	1.59%
39										NA-002	Candidate Name 0008	Party Name AH	1,000	1.59%
40										NA-002	Candidate Name 0009	Party Name AI	15,000	23.81%
41										NA-002	Candidate Name 0010	Party Name AJ	1,000	1.59%
42										NA-002	Candidate Name 0011	Party Name AK	1,000	1.59%
43										NA-002	Candidate Name 0012	Party Name AL	1,000	1.59%
44										NA-002	Candidate Name 0013	Party Name AM	1,000	1.59%
45										NA-002	Candidate Name 0014	Party Name AN	1,000	1.59%
46										NA-002	Candidate Name 0015	Party Name AO	1,000	1.59%
47										NA-002	Candidate Name 0016	Party Name AP	1,000	1.59%
48										NA-002	Candidate Name 0017	Party Name AQ	1,000	1.59%
49										NA-002	Candidate Name 0018	Party Name AR	1,000	1.59%
50										NA-002	Candidate Name 0019	Party Name AS	20,000	31.75%
51										NA-002	Candidate Name 0020	Party Name AT	1,000	1.59%
52										NA-002	Candidate Name 0021	Party Name AU	1,000	1.59%
53										NA-002	Candidate Name 0022	Party Name AV	1,000	1.59%
54										NA-002	Candidate Name 0023	Party Name AW	1,000	1.59%
55										NA-002	Candidate Name 0024	Party Name AX	1,000	1.59%
56										NA-002	Candidate Name 0025	Party Name AY	1,000	1.59%
57										NA-002	Candidate Name 0026	Party Name AZ	1,000	1.59%
58										NA-002	Candidate Name 0027	Party Name BA	1,000	1.59%
59										NA-002	Candidate Name 0028	Party Name BB	1,000	1.59%
60										NA-002	Candidate Name 0029	Party Name BC	1,000	1.59%
61										NA-002	Candidate Name 0030	Party Name BD	1,000	1.59%
62	NA-003	Kingdom Valley	Blue Area 3	Blue Area 3C	318,150	30,500	500	30,000	9.59%	NA-003	Candidate Name 0001	Party Name AA	1,000	3.33%	#N/A	#N/A	#NUM!	#NUM!
63										NA-003	Candidate Name 0002	Party Name AB	1,000	3.33%
64										NA-003	Candidate Name 0003	Party Name AC	1,000	3.33%
65										NA-003	Candidate Name 0004	Party Name AD	1,000	3.33%
66										NA-003	Candidate Name 0005	Party Name AE	1,000	3.33%
67										NA-003	Candidate Name 0006	Party Name AF	1,000	3.33%
68										NA-003	Candidate Name 0007	Party Name AG	1,000	3.33%
69										NA-003	Candidate Name 0008	Party Name AH	1,000	3.33%
70										NA-003	Candidate Name 0009	Party Name AI	1,000	3.33%
71										NA-003	Candidate Name 0010	Party Name AJ	1,000	3.33%
72										NA-003	Candidate Name 0011	Party Name AK	1,000	3.33%
73										NA-003	Candidate Name 0012	Party Name AL	1,000	3.33%
74										NA-003	Candidate Name 0013	Party Name AM	1,000	3.33%
75										NA-003	Candidate Name 0014	Party Name AN	1,000	3.33%
76										NA-003	Candidate Name 0015	Party Name AO	1,000	3.33%
77										NA-003	Candidate Name 0016	Party Name AP	1,000	3.33%
78										NA-003	Candidate Name 0017	Party Name AQ	1,000	3.33%
79										NA-003	Candidate Name 0018	Party Name AR	1,000	3.33%
80										NA-003	Candidate Name 0019	Party Name AS	1,000	3.33%
81										NA-003	Candidate Name 0020	Party Name AT	1,000	3.33%
82										NA-003	Candidate Name 0021	Party Name AU	1,000	3.33%
83										NA-003	Candidate Name 0022	Party Name AV	1,000	3.33%
84										NA-003	Candidate Name 0023	Party Name AW	1,000	3.33%
85										NA-003	Candidate Name 0024	Party Name AX	1,000	3.33%
86										NA-003	Candidate Name 0025	Party Name AY	1,000	3.33%
87										NA-003	Candidate Name 0026	Party Name AZ	1,000	3.33%
88										NA-003	Candidate Name 0027	Party Name BA	1,000	3.33%
89										NA-003	Candidate Name 0028	Party Name BB	1,000	3.33%
90										NA-003	Candidate Name 0029	Party Name BC	1,000	3.33%
91										NA-003	Candidate Name 0030	Party Name BD	1,000	3.33%
Election 2022 (MNA)

Cell Formulas
Range	Formula
H2,H62,H32	H2	=IF(COUNTIFS($J:$J,$J2,$M:$M,"<>")=COUNTIF($J:$J,$J2),SUMIF($J:$J,$A2,$M:$M),"")
I2,I62,I32	I2	=IF(OR(E2="",F2=""),"",F2/E2)
N2:N91	N2	=IF(COUNTIFS($J:$J,$J2,$M:$M,"<>")=COUNTIF($J:$J,$J2),$M2/SUMIF($J:$J,$J2,$M:$M),"")
O2,O62,O32	O2	=IF(COUNTIFS($J:$J,$J2,$M:$M,"<>")=COUNTIF($J:$J,$J2),INDEX($K$2:$K$8161,MATCH(1,($J$2:$J$8161=$A2)*($M$2:$M$8161=LARGE(($M$2:$M$8161)*($J$2:$J$8161=$A2),1)),0)),"")
P2,P62,P32	P2	=IF(COUNTIFS($J:$J,$J2,$M:$M,"<>")=COUNTIF($J:$J,$J2),INDEX($L$2:$L$8161,MATCH(1,($J$2:$J$8161=$A2)*($M$2:$M$8161=LARGE(($M$2:$M$8161)*($J$2:$J$8161=$A2),1)),0)),"")
Q2,Q62,Q32	Q2	=IF(COUNTIFS($J:$J,$J2,$M:$M,"<>")=COUNTIF($J:$J,$J2),INDEX($K$2:$K$8161,MATCH(1,($J$2:$J$8161=$A2)*($M$2:$M$8161=LARGE(($M$2:$M$8161)*($J$2:$J$8161=$A2),2)),0)),"")
R2,R62,R32	R2	=IF(COUNTIFS($J:$J,$J2,$M:$M,"<>")=COUNTIF($J:$J,$J2),INDEX($L$2:$L$8161,MATCH(1,($J$2:$J$8161=$A2)*($M$2:$M$8161=LARGE(($M$2:$M$8161)*($J$2:$J$8161=$A2),2)),0)),"")
F2,F62,F32	F2	=IF(H2="","",H2+G2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F32:F8161	Cell Value	>$E$2	text	NO
F32:F8161	Cell Value	>0	text	NO
H32:H8161	Cell Value	>$E$2	text	NO
M3:N8161	Expression	=IF(COUNTIFS($J:$J,$J3,$M:$M,"<>")=COUNTIF($J:$J,$J3),AGGREGATE(14,6,($M$2:$M$8161)/($J$2:$J$8161=$J3),1)=$M3,FALSE)	text	NO
M3:N8161	Expression	=IF(COUNTIFS($J:$J,$J3,$M:$M,"<>")=COUNTIF($J:$J,$J3),AGGREGATE(14,6,($M$2:$M$8161)/($J$2:$J$8161=$J3),2)=$M3,FALSE)	text	NO
M2:N2	Expression	=IF(COUNTIFS($J:$J,$J2,$M:$M,"<>")=COUNTIF($J:$J,$J2),AGGREGATE(14,6,($M$2:$M$8161)/($J$2:$J$8161=$J2),1)=$M2,FALSE)	text	NO
M2:N2	Expression	=IF(COUNTIFS($J:$J,$J2,$M:$M,"<>")=COUNTIF($J:$J,$J2),AGGREGATE(14,6,($M$2:$M$8161)/($J$2:$J$8161=$J2),2)=$M2,FALSE)	text	NO
F2:F31	Cell Value	>$E$2	text	NO
F2:F31	Cell Value	>0	text	NO
H2:H31	Cell Value	>$E$2	text	NO

 

[mwvirk]

If adding the NA (or any other 2 digit) code is required, then I am OK to un-merge the A row rather inputting it again in J row. We can make use of the existing similar data as long the requirements met.

 

[KRice]

I don't see any problem with the formulas that are producing errors; however, you will need to enter those formulas as array formulas. To do that, click on each cell, then click in the formula bar and hit F2 to go into Edit mode, then hit Ctrl-Shift-Enter simultaneously. You should see curly brackets surround the formula, indicating Excel is treating it as an array formula. Let me know if that makes the #N/A errors go away and if the expected results are delivered.

 

[mwvirk]

getting the below error

I don't see any problem with the formulas that are producing errors; however, you will need to enter those formulas as array formulas. To do that, click on each cell, then click in the formula bar and hit F2 to go into Edit mode, then hit Ctrl-Shift-Enter simultaneously. You should see curly brackets surround the formula, indicating Excel is treating it as an array formula. Let me know if that makes the #N/A errors go away and if the expected results are delivered.

then i un-merge the cells for the first record and followed your instructions and it worked and i got the results:

and then mergred the cell for the 1st record and applied the same cells toward the end of the sheet.

it's working now. however the sheet is dead slow

thank you once again.

 

[KRice]

Oh...yes, that is a good reminder and another reason to avoid merged cells. It sounds as if this reason alone makes array formulas impractical with merged cells and older versions of Excel (Excel 365 handles array formulas natively so I did not encounter this problem, even with merged cells). I will have another look at the formulas to see where some performance improvements could be made. If you are willing to consider the other format (with separate tables for: 1) organizing candidates in each voting region, 2) describing the voting regions and final results, and 3) tabulating voting results), then I wonder if you might also consider building the input side of the Voting Tabulation table (columns J:L in your example above) using Power Query? PQ would build the long table needed for tabulating votes, but there would be no formula "overhead". Once the file is setup with PQ, then the table would be built by renewing any candidate lists and hitting Data > Refresh All to construct the voting tabulation table.

 

[mwvirk]

Oh...yes, that is a good reminder and another reason to avoid merged cells. It sounds as if this reason alone makes array formulas impractical with merged cells and older versions of Excel (Excel 365 handles array formulas natively so I did not encounter this problem, even with merged cells). I will have another look at the formulas to see where some performance improvements could be made. If you are willing to consider the other format (with separate tables for: 1) organizing candidates in each voting region, 2) describing the voting regions and final results, and 3) tabulating voting results), then I wonder if you might also consider building the input side of the Voting Tabulation table (columns J:L in your example above) using Power Query? PQ would build the long table needed for tabulating votes, but there would be no formula "overhead". Once the file is setup with PQ, then the table would be built by renewing any candidate lists and hitting Data > Refresh All to construct the voting tabulation table.

don't know what it would look like but if you think I cannot create the problem while working on the dashboard (shown in post #24) then I can try.
there was an other one you have shared but I really didn't understand how I can add more candidates and NA# etc

 

[KRice]

Could you clarify a detail we discussed previously. If candidates a, b, and c are the only 3 candidates in NA-001, is it true then that NA-002, NA-003... (all of the NA's) also have only candidates a, b, and c, and no other candidates?

I understand your earlier explanation that some candidates might appear in other two-letter code categories. What do you call these two-letter codes with numbers...are they voting stations, precincts, districts? (I don't know what words to use to describe them).

 

[mwvirk]

Could you clarify a detail we discussed previously. If candidates a, b, and c are the only 3 candidates in NA-001, is it true then that NA-002, NA-003... (all of the NA's) also have only candidates a, b, and c, and no other candidates?

I understand your earlier explanation that some candidates might appear in other two-letter code categories. What do you call these two-letter codes with numbers...are they voting stations, precincts, districts? (I don't know what words to use to describe them).

OK. let us make it clear to understand easily.

'NA' stands for National Assembly and has almost 250 to 300 from all over the country (it might be 250 or more or less - but this is the expected range of 250-300)
different areas are divided and so it goes like this: NA-001, NA-002... and so on.

Then the four provinces come into the country with different 2-digit codes - each province has a separate code and a number like PP-001, PP-002... and so on. similarly the other 3 provinces: PB, PS, KP

so, are the NA# PP# PB# PS KP# 2-digit clear?

Don't get confused with the candidate names. As I told you, these are the names (assume "Candidate Name 0001" is a first, second, and third name of a person = Michael Steven John) (so, you can read it like Candidate=Michael Name=Steven 0001=John) so, I am assuming 30 candidates from each 2-digit area. Now if the candidates are 30 then they must be having 30 different parties. 1 candidate cannot participate in 2 parties under 1 area (NA, PP, PS, PB, KP) but it is possible that we have 30 candidates from 25 different parties but the remaining 5 are 'Independent'

Similarly, the party name "Party Name AA" can be 3 words of any party.

in my sheet, I made all unique names for candidates and parties.

candidate name can be anything. any name you think can be put in any 8161 K2:K8161 rows but most of the names can be the same with a different party or the same party.
If KRice is participating from 1 NA# then he can be in any other NA# but your party will be the same. It's like you are presenting your party from multiple areas. Your every victory in any area will give 1 win to your party. Now you can be in multiple NA# but you cannot be from all the 300 NA# (maybe you can in 2, 5, 6, NA#)
Same way, you can be in NA# but also from any other province.

Hope it is clear!

(I have sent a private message if you have received it and if that's possible)

 

[mwvirk]

1 candidate cannot participate in 2 parties under 1 area (NA, PP, PS, PB, KP)

this is applicable in all areas.
e.g. if KRice is NA#1 under the party XYZ then he cannot be part of any other party.

let's make it more clear... 1 physical person belongs to 1 party. he is the asset of 1 party. he cannot participate for any other party from any NA PP PS PB KP

 

[mwvirk]

most of the names can be the same with a different party or the same party.

name can be the same. but physically person will be different