1

I am trying to to use the Array formula that includes a minus and divide value. Here is my formula:-

IF(J3:J="",,SUM(((B3:B+C3:C)+(E3:E+F3:F))-$X$3)/$X$3)

I tried inserting MINUS( before the SUM but this didn't evaluate correctly. As far as the Divide goes I have tried to fit this in next to the MINUS, but this through up an error. I have read that minus and divide are not like by array formula but there is a workaround. could I ask for some help please in getting the above formula to operate as an array.

Improve this question
5
  • Welcome to Web Applications. Please edit the question to add some sample input data, the number of rows having values, sheet number of rows (in other words tell if there empty rows below the data) and the expected result for the sample data.
    – Rubén ♦
    Jan 5, 2022 at 16:28
  • Remove the word SUM from your equation, leaving everything else the same (including all sets of parentheses), and see if you get the result you expect.
    – Erik Tyler
    Jan 5, 2022 at 17:06
  • I have added a sheet for your viewing. The suggestion you offered, I trialled and found the sum part of the formula worked the rest did not work. hope this helps. docs.google.com/spreadsheets/d/…
    – Del D
    Jan 5, 2022 at 20:13
  • Hi. Thanks for including your spreadsheet. 1) It references $O$3 (instead of $X$3), would you please edit the cell reference in your question formula for consistency. 2) There are many possible ways for this formula to be written (with and without ARRAYFORMULA) would you please edit your spreadsheet to clearly include the answer that you are trying to calculate. 3) Column J includes a value in each row, would you please describe your expectations for the formula if there was not a value in each row.
    – Tedinoz
    Jan 6, 2022 at 2:23
  • Hi Tedinoz 13 Thanks for taking a look at this issue I have. I have amended the sheet and changed the reference to x and not o. Also I have listed in red the expected values. I would prefer to use array formula as the sheet is plus 700 rows and growing. the Column J will always be occupied i.e. it will hold the date that the row was edited. the date will always be from Monday too Friday (5 days)
    – Del D
    Jan 6, 2022 at 17:12

1 Answer 1

Reset to default
0

Try this formula:

=arrayformula(IF(J3:J="",,((B3:B+C3:C)+(E3:E+F3:F)-$O$3)/$O$3))

SAMPLE

sample

Note: row#6 = 1.133 Vs your expected answer = 1.073

Improve this answer
1
  • Many thanks Tedinoz for your help and time, it is much appreciated
    – Del D
    Jan 8, 2022 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.