Question description:
This user has given permission to use the problem statement for this
blog.
I have a formula which I am trying to append, I would like to add d2:d301 to it. I've inherited the formula so not sure how to add to it
current formula is
=iferror(if(row()<>2,INDEX(arrayformula(filter($P$2:$P301,$C$2:$C301<>"",row($C$2:$C301)=max(if($C$2:$C301=C302,row($C$2:$C301),0)))) ,1),0),0)
Solved by A. H. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
08/07/2018 - 04:28
Hi..
User
08/07/2018 - 04:29
hi
Excelchat Expert
08/07/2018 - 04:29
do you have the data where the formula is being used?
Excelchat Expert
08/07/2018 - 04:29
it will help me in debugging the formula better..
User
08/07/2018 - 04:29
yes but can't share it
User
08/07/2018 - 04:30
has company confidential info
Excelchat Expert
08/07/2018 - 04:30
ohh.. ok.. i understand.. no problem..
Excelchat Expert
08/07/2018 - 04:30
can you explain the context of this formula?
Excelchat Expert
08/07/2018 - 04:31
also are you having this formula in google sheet?
User
08/07/2018 - 04:32
two accounts owners have same product and need file to recognise they are seperate owners colume D has owner name, colume c is product
User
08/07/2018 - 04:32
yes google sheets
Excelchat Expert
08/07/2018 - 04:33
can you please help me recreate a fake scenario of the data on this googlesheet here...
User
08/07/2018 - 04:34
column p tracts all costs of product associated with the buy or sell
Excelchat Expert
08/07/2018 - 04:34
i have got an idea of the formula.. but to debug it will need some data..
User
08/07/2018 - 04:35
ok can you see the sheet to the right?
Excelchat Expert
08/07/2018 - 04:36
yes..
User
08/07/2018 - 04:36
ok I ll type in there
Excelchat Expert
08/07/2018 - 04:41
Hi are you there??
User
08/07/2018 - 04:44
dont worry about sector "ref" it copied over bad. it's fine on mine
Excelchat Expert
08/07/2018 - 04:44
ok.. in which cell are you using this formula?
User
08/07/2018 - 04:45
colunm n is where formula is
Excelchat Expert
08/07/2018 - 04:46
Thx.. for the data.. with this info.. let me link the question posted earlier..
Excelchat Expert
08/07/2018 - 04:46
give me a few mins..
Excelchat Expert
08/07/2018 - 04:49
can you please updated the data for column P.. without the formula?
Excelchat Expert
08/07/2018 - 04:49
i see from the formula its a key column...
User
08/07/2018 - 04:50
you just want raw numbers
Excelchat Expert
08/07/2018 - 04:50
yes..
Excelchat Expert
08/07/2018 - 04:54
ok.. i kinda got the data..
User
08/07/2018 - 04:55
but previous cost should read 0, 0, cause owner is different
User
08/07/2018 - 04:55
for n2 and n3
Excelchat Expert
08/07/2018 - 04:56
i've not yet.. done the formula..
Excelchat Expert
08/07/2018 - 04:56
so now you need to get the previous cost for the same owner?
User
08/07/2018 - 04:56
yes just explaining what I think it
User
08/07/2018 - 04:57
yes each owner should get their own, but right now it sums up regardless of owner it ids just the product
Excelchat Expert
08/07/2018 - 05:00
=iferror(if(row()<>2,INDEX(arrayformula(filter($P$2:$P2,$C$2:$C2<>"",$D$2:$D2=D2,row($C$2:$C2)=max(if($C$2:$C2=C2,row($C$2:$C2),0)))) ,1),0),0)
Excelchat Expert
08/07/2018 - 05:00
can you please paste this updated formula in cell N2?
Excelchat Expert
08/07/2018 - 05:00
in your googlesheet?
Excelchat Expert
08/07/2018 - 05:00
and drag this formula all the way till cell 302..
Excelchat Expert
08/07/2018 - 05:01
drag the formula = at the bottom right corner of the cell.. click that and drag it downwards until cell 302
User
08/07/2018 - 05:02
ok going now
User
08/07/2018 - 05:06
not quite right it add the numbers look way too big.
User
08/07/2018 - 05:07
what i need it to do is filter product, the owner, and then add the cum cost
Excelchat Expert
08/07/2018 - 05:08
also should it be for the same product?
Excelchat Expert
08/07/2018 - 05:08
from cell C1?
User
08/07/2018 - 05:08
yes
Excelchat Expert
08/07/2018 - 05:09
the last formula you shared was checking if product column is not blank..
Excelchat Expert
08/07/2018 - 05:09
ohh ok.. let fix that problem also.
Excelchat Expert
08/07/2018 - 05:09
one question... will the product column be blank??
Excelchat Expert
08/07/2018 - 05:10
based on the data i dont think so..
User
08/07/2018 - 05:10
no product will be not be blank
User
08/07/2018 - 05:10
nor owner
Excelchat Expert
08/07/2018 - 05:10
got it..
Excelchat Expert
08/07/2018 - 05:10
can you please use this updated formula???
Excelchat Expert
08/07/2018 - 05:11
iferror(if(row()<>2,INDEX(arrayformula(filter($P$2:$P2,$C$2:$C2=C2,$D$2:$D2=D2,row($C$2:$C2)=max(if($C$2:$C2=C2,row($C$2:$C2),0)))) ,1),0),0)
Excelchat Expert
08/07/2018 - 05:11
repeat the same step from N2 till N302
User
08/07/2018 - 05:14
no not right. sorry
Excelchat Expert
08/07/2018 - 05:15
hmm.. for sampling.. i'm updating the product and account holder to same ..and testing..
User
08/07/2018 - 05:16
ok
Excelchat Expert
08/07/2018 - 05:18
from the data value of cell N3 should have been $1619.93?
Excelchat Expert
08/07/2018 - 05:18
and value of cell N4 should have been 1834.95?
Excelchat Expert
08/07/2018 - 05:18
correct?
User
08/07/2018 - 05:19
with the original formula yes, but I need it to say 0, then when it is sold in row 4 the previous cost should read 28590
User
08/07/2018 - 05:20
andn row 5 previous cost s/b 1619.93
Excelchat Expert
08/07/2018 - 05:21
hmmm.. you did not mention about using the Buy/Sell ... also.. the formula doesnt take ref. from column "Transaction value with F/X"
Excelchat Expert
08/07/2018 - 05:22
i have only updated the previous formula to include "account Held" column and "Product" column..
User
08/07/2018 - 05:22
not worried about f/x
Excelchat Expert
08/07/2018 - 05:23
so you want only Buy/Sell?
User
08/07/2018 - 05:24
buy sell doen't effect formula, the transacted units have + or -ve that drive it in column m
User
08/07/2018 - 05:24
coumn g
Excelchat Expert
08/07/2018 - 05:25
hmm.. we are left with only 4mins for this session...
Excelchat Expert
08/07/2018 - 05:26
so if i understand the whole requirement correctly.. for a Buy/Sell against an account holder.. you need to the previous max value from column P?
Excelchat Expert
08/07/2018 - 05:26
correct?
User
08/07/2018 - 05:26
yes
Excelchat Expert
08/07/2018 - 05:26
please use the formula..
User
08/07/2018 - 05:26
account and product.
Excelchat Expert
08/07/2018 - 05:26
=iferror(if(row()<>2,INDEX(arrayformula(filter($P$2:$P2,$C$2:$C2=C2,$B$2:$B2=B2,$D$2:$D2=D2,row($C$2:$C2)=max(if($C$2:$C2=C2,row($C$2:$C2),0)))) ,1),0),0)
Excelchat Expert
08/07/2018 - 05:27
i have now included the criteria for account and product...
User
08/07/2018 - 05:27
account holder can buy many products and then sell them later
Excelchat Expert
08/07/2018 - 05:27
the new formula includes the criteria for Type also.. Buy/Sell
Excelchat Expert
08/07/2018 - 05:28
we are running out of time..
User
08/07/2018 - 05:28
right orginal formula only accounted for product not account holder
Excelchat Expert
08/07/2018 - 05:28
marking the session as done..
Excelchat Expert
08/07/2018 - 05:28
please validate the formula.. as previously..
User
08/07/2018 - 05:28
can you help?
Excelchat Expert
08/07/2018 - 05:28
and provide a feedback
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.
