CODEWITHSUNDEEP
arraysdatabasevbaexcel
user5627819
user5627819

Reputation:

Excel: Array Formula Calculates Once

Overview

Hello everyone,

I'm currently designing a basic financial Excel workbook that will constantly receive an inflow of data, and calculate a few statistics on it for planning purposes. The idea is that the user will type in their income for that period, and the workbook will update its statistical data instantly.

Actual Issue

The problem is when I want to find the lowest value greater then zero. I will type my array formula into the box I want to display it in, and press the appropriate keystroke, and it will just change the box value to the number. For example:

  1. Formula entered, keystroke applied

Array: {-2,0,2,4} Result: 2

  1. Array Updated

Array: {-2,0,1,2,4} Result: 2

So the array formula isn't updating, and I actually think it might be disappearing once I hit the keystroke.

Details

Here is a detailed description of what I did

1 - First, I entered the formula and hit the keystroke

 MIN(IF(A1:A10>0,A1:A10))

^ Once entered, I then input keys 'Command' , '+' , 'Return' on my Mac. It is the equivalent of 'CTRL' , 'SHIFT' , 'Enter' on a Windows

2 - Next, It returns the desired result, but clears the formula out

2

Then if new data is entered into the array, it won't update. If I want it to update, I will have to manually do step one again.

What I need

Either...

A. A way to create an array formula that will constantly update to new data

or...

B. Another solution that won't require this weird array formula stuff, and thus work as all my other non-array formulas have.

Side Note: There is another post called Excel formula calculating once then deleting, and no it is not the same as this one. I thought it was the answer to my problem, but it is not. Don't flag this as a repeat insight of that post.

Upvotes: 2

Views: 339

Answers (1)

EoinS
EoinS

Reputation: 5482

Your formula looks correct! It should function properly.

If I had to guess, this is probably because Calculation is set to manual. Do your other non-array formulas auto-update when changes are made to data?

Fix: Mac Excel 2008: Excel Preferences > Calculation > Automatically

If you are still having trouble, post some more information on the workbook, whether you are using macros, how and where your data is being stored.

Assuming that your array is being stored in column A; your updated data (1 in this case) might be entered outside of the range A1:A10 . You may have to refit your formula to cover a larger range, especially if you are going to be adding data to the array.

There are alternatives to using Arrays: -Using normal Excel formulas you could add a reference column in column B. Then enter something like this to B1 =IF(A1<0,"",A1) Drag this column down and then for your solution use =MIN(B1:B10) -If you want to go the VBA route, I can update my post to help.

Upvotes: 0

Related Questions