Ask Your Question
0

Conditional product using a fixed row on one sheet, varying row on another?

asked 2019-07-05 22:34:26 +0200

eng442 gravatar image

I have two spreadsheets like this:

Purchases

+---+----------+-------+------------+---------------------+
|   | A        | B     | C          | D                   |
+---+----------+-------+------------+---------------------+
| 1 | Date     | Asset | Avg. price | Adjusted avg. price |
+---+----------+-------+------------+---------------------+
| 2 | 06/26/19 | ABCD1 | 60         | 10                  |
+---+----------+-------+------------+---------------------+
| 3 | 07/29/19 | WXYZ1 | 60         | 60                  |
+---+----------+-------+------------+---------------------+
| 4 | 08/29/19 | ABCD1 | 60         | 60                  |
+---+----------+-------+------------+---------------------+

Events

+---+----------+---------------+-------+------------+
|   | A        | B             | C     | D          |
+---+----------+---------------+-------+------------+
| 1 | Date     | Event         | Asset | Multiplier |
+---+----------+---------------+-------+------------+
| 2 | 06/26/19 | Reverse split | ABCD1 | 6          |
+---+----------+---------------+-------+------------+

I want Adjusted avg. price to be Avg. price x 1/Multiplier if Asset is the same on both columns and if Events.Date >= Purchases.Date and Event = "Reverse split".

I am trying to do this one step at a time. As far as matching the asset goes I used the following formula for column D of the purchases sheet:

IF($Events.C2=B2, 1/$Events.D2*C2, C2)

Then I selected the row and pulled down the mouse to apply the formula to all rows in the column. As can be seen in the purchases sheet above, this works for row 2, but does not work for row 4, as the formula becomes

IF($Events.C4=B4, 1/$Events.D4 * C4, C4)

I also tried using PRODUCT(IF($Events.C:C=B:B, 1/$Events.D:D, 1), C:C) or something of the sort (I lost the formula), but the result was the same.

I can see two easy workarounds:

  1. Filling in the formula manually for each row
  2. Having one sheet for each event with as many rows as the purchases sheet

However, this does not scale, as I expect hundreds of rows on both sheets. Is there any way I can do this using Calc's formulas, or should I go for a macro or just abandon Calc altogether and use a programming language and a database?

edit retag flag offensive close merge delete

Comments

I think the Absolute&Relataive references should help about how to manage that formulas

m.a.riosv gravatar imagem.a.riosv ( 2019-07-06 13:57:03 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-07-06 17:26:39 +0200

The Events worksheet only has one item, and everyone has to compare with it, is that it?

If YES use the formula: =IF($Events$C$2=B2,1/$Events.$D$2*C2,C2), so C2 and D2 of Events are fixed.

-----------------------------------------------------------------------------

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball Descrição da imagem to the left of the answer, to finish the question.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-07-05 22:34:26 +0200

Seen: 18 times

Last updated: Jul 06