Converting VLOOKUP and IF Formulas to Array CSE Formulas

[truk2]

I am running a rather large spreadsheet almost all with formulas of varying complexity. To improve calculation and to prevent user errors overwriting cells I have been converting what formulas I can to array CSE formulas.

I have formula in all columns from C:AZ. They all cover from row 77 to row 652

I management the simple formulas easily enough, but need some help with functions that I haven't been able to work out. I have been researching, but I suspect that the problem is my understanding of array formulas.

for example

Please Login or Register  to view this content.

is filled down across G77:G652

I tried highlighting the range, entering

Please Login or Register  to view this content.

+ CSE
but got very different results

Similarly I am unable to convert a simple IF() statement

Please Login or Register  to view this content.

I have a lot of formulas similar to these ones, and I would really like to upskill in array formulas anyway
It's not vital, but any help would be greatly appreciated

[FDibbins]

1st thing to mote is that ARRAY formulas, if used extensively, and on large amounts of data, can actually slow your file down.

2nd, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[truk2]

upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Hi @FDibbins
I hadn't included a sample file because as I'm only asking how to replace fill down formulas, I didn't think it was worth it. But I'll set one up now.

1st thing to mote is that ARRAY formulas, if used extensively, and on large amounts of data, can actually slow your file down.

Could you please give more feedback on how/why Array formulas would slow the workbook down?
Other sources said that it tends to speed up, and that storage is reduced as (in my case) 700 formulas get replaced with one.
I'm interested because so far I've reduced the the file size a little and it does seem marginally faster. I was going to do a speed test once I had managed to change a few more formula columns

[FDibbins]

Basically, excel recalcs everything inside the array, if there are ANY changes in ANY cells that are referenced by the array. With regular formulas, if nothing changes in the range, nothing recalcs.

See here
https://www.excelforum.com/excel-gen...w-down-xl.html

and also take a look at some links in this search
https://www.google.com/search?rlz=1C...71.olZ1hsTTKt4

Frome here...
https://trumpexcel.com/suffering-fro...-spreadsheets/

Excel array formulas - limitations and alternatives
Array formulas are by far one of the most powerful features in Excel, but not all-powerful. Here are the most critical limitations of arrays in Excel.

1. Large arrays may slow down Excel
Though Microsoft Excel does not impose any limit on the size of arrays you use in your worksheets, you are limited by memory available on your computer because recalculating formulas with large arrays is time consuming. So, in theory, you can create huge arrays consisting of hundreds or thousands of elements, in practice this is not recommended because they can drastically slow your workbooks.

2. Entire column arrays are not allowed
You are not allowed to create an array that includes a whole column or several columns for an obvious reason explained above. Array formulas in Excel are very resource-hungry and Microsoft is taking preventive measures against Excel's freezing.

3. Limit to array formulas referring to another sheet
In Excel 2003 and earlier versions, a given worksheet could contain a maximum of 65,472 array formulas referring to another sheet. In modern versions of Excel 2013, 2010 and 2007, cross-worksheet array formulas are limited by available memory only.

4. Debugging array formulas
If your array formula returns an incorrect result, make sure you pressed Ctrl + Shift + Enter when entering it. If you did, select parts of the formula and press the F9 to evaluate and debug them.

5. Alternatives to array formulas.
If you find Excel array formulas too complex and confusing, you can use one of Excel functions that can naturally process arrays of data (without pressing Ctrl + Shift + Enter). A good example is the SUMPRODUCT function that multiplies values in the specified arrays and returns the sum of those products. Another example is Excel INDEX function with an empty value or 0 in the row_num or col_num argument to return an array of values from the entire column or row, respectively.

If you want to download the Excel array formula examples discussed in this tutorial to reverse-engineer them for better understanding, you are most welcome to download formula examples. This is an .xlsm file since example 6 includes a custom VBA function, so you will have to click the Enable Content button after downloading to allow the macro to run.

[truk2]

Sorry it took me a while to get back to this.

Here is an example with a section of my data

It looks a bit messy because of the section that I removed due to broken links, and area that I clear to keep things private.

There is a few table for vlookups in the top 70 rows, and a few cells used as multipliers. These a changed and repopulated via vba.

from rows 77 to 652 is data organised by month, with formulas refering to the 1's and 0's in the left most columns.

I was mostly wanting to understand how to convert formulas such as those in columns J and K. Even if you convince me that this is a bad idea, I still want to understand how, so that I can get better at this.

Thanks and regards,
Truk2

[FDibbins]

If you are referring to the formulas...
J77=$H77*$I$27*VLOOKUP($A77,$H$6:$N$18, IF($B77=0,6,7))
K77=IF(F77 > I77, F77-I77,0)

Im not sure how/why you would want to "array" these?

Perhaps if you explain what you are trying to achieve/improve on, we could better understand?

[Phuocam]

Maybe?

=$H77:$H652*$I$27*IF($B77:$B652=0,VLOOKUP($A77:$A652,$H$6:$N$18, 6),VLOOKUP($A77:A652,$H$6:$N$18, 7))

[truk2]

@FDibbins, Initially I was following advice that array formulas will lead to a performance increase. That has now been debunked in my head. But I still wanted to improve my understanding of how to implement arrays so that I am more prepared in the future. Anything with functions or conditionals is currently a significant block for me when it comes to arrays. I also still want to try a time test after I impement a few more arrays.

@Phucam
Thanks. I'll give that a go
Was it necessary to avoid putting the if statement inside the Vlookup?

[truk2]

@Phucam

That worked perfectly, and now that I look at it, I'm really not sure why it gave me trouble
I implemented the if statement the same way, and that worked too. I really can't understand why it was giving me errors when I first posted this.

I ended up with
J77:J652 {=$H77:$H652*$I$27*VLOOKUP($A77:$A652,$H$6:$N$18,IF($B77:$B652=0, 6,7))}
K77:K652 {=IF(F77:F652 > I77:I652, F77:F652-I77:I652,0)}

I guess I'm learning.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[truk2]

Sorry, thought that I already had