Can't Drag Formula

Question

Level 1

51 points

Can't Drag Formula

Hi

I am trying to drag this formula D3 of the lower table but it doesn't over write the previous incorrect values?:

IF(ISBLANK(Strategy::$D$3), 0,
   IF(LEFT(Strategy::$D$3, 2) = "LC",
      IF($A3 < Strategy::$D$9, MINUS(Strategy::$D$13), MAX($A3 - Strategy::$D$9, 0) - Strategy::$D$13),
      IF(LEFT(Strategy::$D$3, 2) = "LP",
         IF($A3 < Strategy::$D$9, MINUS(Strategy::$D$13), MAX($C$6 - B36, 0) - $C$23),
         ""
      )
   )
)

It was difficult with Column F of the same second table but I replaced the data format "Multiple" with "Number" for the rows below where I was dragging down and it worked but not for Col D?

MacBook Pro 15″

Posted on Jun 9, 2023 1:22 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,348 points

Jun 9, 2023 5:49 AM in response to Bardonicloud

I am assuming you were able to drag the formula down to the other cells but you are not happy with the formula results. Your formula uses a lot of absolute addressing that is locking in the row number for the cell references. $D$3 for example will always refer to cell D3 no matter which way you drag the formula, $D$9 will always refer to D9, and there are others. Maybe one or more of those references should not be locked in like that.

Reply

Answer 2

SGIII

Level 8

37,532 points

Jun 9, 2023 5:33 AM in response to Bardonicloud

If you've got the formula working in one cell and your having trouble dragging the yellow dot at its bottom border to fill down the column, then you can always select the cell with the working formula, command-c to copy, select the cells below where you want the formula as well, and command-v to paste.

BTW, that looks like a complicated nested if formula. You might find it simpler to use the IFS function instead.

SG

Reply

Answer 3

Bardonicloud Author

Level 1

51 points

Jun 9, 2023 1:29 AM in response to Bardonicloud

This is the preferred non working code:

Strategy::$D$11×Strategy::$D$12), IF(LEFT(Strategy::$D$3, 2) = "LP", IF($A3 < Strategy::$D$9, −(Strategy::D10×Strategy::$D$11×Strategy::$D$12), MAX($C$6 − B36, 0) − Strategy::D10×Strategy::$D$11×Strategy::$D$12),          ""       )    ) )

Reply

Answer 4

Bardonicloud Author

Level 1

51 points

Jun 10, 2023 1:37 AM in response to Badunit

Actually, the whole MacBook was misbehaving... slow then normal... Having opened up the Activity Monitor I found my CPU spiking to 600% (kernel task -- root) then returning to normal after 5-10 mins. The last time this happened was due to the fans being clogged with dirt so Im booking it in! As long as I only have Numbers open when it starts spiking I can quit the app and relaunch once the kernel tasks have returned to normal levels.

Reply

Answer 5

Bardonicloud Author

Level 1

51 points

Jun 9, 2023 1:38 AM in response to Bardonicloud

In fact I cleared Cell D3 and dragged down Column D with the same lack of effect. It's not the formula causing issue.

Reply