How to Create Array Formulas in Excel

[@johnq3543]

This was very helpful to me. Thank you

[@docsavetheworld]

great video !

[@spockrates7980]

Great video! But I have a question. How would I go about creating an array formula with an undefined second cell reference? You can do this to some extent in Excel via something like A:A, but in Google Sheets there's an additional level of control, as you can specify something like A4:A. Is there similar functionality for Excel? Thanks in advance! EDIT: Vertex helped me figure it out, if anyone else is wondering. The trick is to use a count of the whole range as the second reference, as Excel allows you to use formulas as part of an array reference. Example: =ARRAYFORMULA(A4:A) in Google Sheets becomes A4:INDEX(A:A,COUNTA(A:A),1) in Excel.

[@vertex42]

The A4:A type of reference in Google Sheets means "starting at A4 and extending to the last cell in the column". In Excel, you can use INDEX or OFFSET as the second part of the reference if you want to dynamically define the end of the range. Like this for example: A4:INDEX(A:A,15). If you want to extend the range to the last value or last non-empty cell, or something like that, there are methods you can google like "return last cell in column"

[@spockrates7980]

@@vertex42 Ok, I'll Google that. Thanks!

[@deez9805]

Is there a way to make this formula availablr? Im running version 16.45 and the arrayformula function as well as the filter function is not available. When i search it in the formulas list ithey dont even show up so when i type it in cells, i get errors. This is very frustrating because NO ONE has been able to find a solytion. Ive looked all over the internet trying to find a solution to this problem and ive found one person (from 10 years ago) who had the same issue but they weren't very clear as to what is going on. I know i could copy the formula cell by cell but that isnt practical since the information i need to fill the cells with is dynamic. PLEASE HELP ILL PAY LMAO

[@TODOMATIO]

I’m trying to creat a basic daily balance. All I want is the formula that will take yesterday’s balance, minus today’s expenses, plus today’s income (this will be today’s balance)

[@vertex42]

See the following post: www.vertex42.com/blog/excel-formulas/create-a-running-balance-in-excel.html

[@sumanthakur007]

The formula in Cell C78, kindly review.

[@raghasudhamadhan2838]

I tried the same formula but it is not working

[@AbdullahAbuhimd]

How can I do it on macOS?

[@vertex42]

I think it's Command+Return on a Mac.

[@sahyunmin]

Control + Shift + Return

[@Twinkier1]

Why use an array formula when you can put the formula for the product in the cell and drag it down to the rest of the cells? It appears that you arrive at the exact same result. What's the advatage then?

[@vertex42]

This is just a simple example used to show how to create an array formula. In this particular example, an array formula makes it unnecessary to include the Completed column.

[@ralphschraven339]

The advantage is that you don't need to use a column; you can do the entire calculation within the formula. This can be very useful, you may imagine, when doing loads of these in one document. Imagine calculating a dozen or so statistics, and instead of neatly displaying them in a single column, they take up more than a screen full of irrelevant data to arrive at the end results. Sheets are for humans, so using up "visual space" is a real issue.

[@mahaboobhossain5076]

I NEED THIS PROJECT FILE SISTER PLZZZZZZZZZZZZZZZZZZZZZZZZ

[@vertex42]

Visit the link in the description to download the file.