0

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.

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 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 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 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 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 at 17:12
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

1
  • Many thanks Tedinoz for your help and time, it is much appreciated
    – Del D
    Jan 8 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.