Formula not copying down in spreadsheet

[SpreadsheetGirl]

Hi All,

I have a spreadsheet with a formula I tried dragging down, and even though it copies the formula correctly, the results remain the same in the entire column. I tried F9 / checking formula settings were set to automatic but that did not sort the problem either. Anyone able to help?

Thanks!

 

[philwojo]

Can you post your formula.

 

[Dobo_Bobo]

Go to Excel > File > Options > Calculation options

In there you will see three options:

Automatic
Automatic except for data tables
Manual

I don't know if there are any, but my guess is you've unknowingly pressed a shortcut key that sets the option to manual.

 

[SpreadsheetGirl]

Hi guys,

thanks for the responses - it is definitely not the automatic manual setting, I already checked on that one, and by pressing F9 this should have sorted if it had been set to manual..

There is no way I can attach anything here is there? I think a screenshot would be easiest - basically I have column A which is customer IDs, then column B is date of order. Column C is first date of last order with formula =MAX(IF($A$2:$A$5000=A2,$B$2:$B$5000)) and column D first order date with formula =MIN(IF($A$2:$A$5000 = A2, $B$2:$B$5000)) . Both formulas have the same issue. I am trying to determine first and last order date for some customer orders so I can determine the lifespan. Obviously not every customer has ordered more than once, there are thousands of orders.

I found the first and last order date formula online but not sure why it is not working. Can you help?



Thanks!

 

[Eric W]

Welcome to the forum.

At the risk of stating the obvious, are you aware these are array formulas? In order to get an array formula to work, you have to enter it in the formula bar, and instead of pressing Enter when you're done, you press Control+Shift+Enter. Then you can copy it and paste it down the column. Without doing that, the formula will not work right, and might be the reason you're getting bad results.

As an alternative, if you have an Office 365 subscription, you can use this non-array formula:

=MAXIFS($B$2:$B$5000,$A$2:$A$5000,A2)

This is entered the usual way, but it's only available in certain versions of Excel. The MINIFS works the same way.

 

[SpreadsheetGirl]

Hi Eric,

thanks, no I was not aware - I am not even sure I know what array formulas are? I have office 360, so I will try the non array formula you have posted.

Thanks a lot for the help!

 

[SpreadsheetGirl]

Hi All,

I have tried the non array formula but it doesn't work. I have noticed that some of my dates have a funny format. So the ones that are displaying correctly change to a number when I change them from date to text format. However, the ones that seem to have an issue don't turn to a number when I switch the formatting from date to text it still displays as date. e.g. the correctly working date is 31/12/2017 and turns to 43100 when switched from date to text format. The date that seems to have a bug is 01/01/2014 and stays 01/01/2014 when I switch the cell from date to text format. can you help?

Thanks!

 

[Dobo_Bobo]

Hi All,

I have tried the non array formula but it doesn't work. I have noticed that some of my dates have a funny format. So the ones that are displaying correctly change to a number when I change them from date to text format. However, the ones that seem to have an issue don't turn to a number when I switch the formatting from date to text it still displays as date. e.g. the correctly working date is 31/12/2017 and turns to 43100 when switched from date to text format. The date that seems to have a bug is 01/01/2014 and stays 01/01/2014 when I switch the cell from date to text format. can you help?

Thanks!

Hi SpreadsheetGirl

I think Eric has cracked your problem, so to explain it further.

If you use the formula without the { } it will not work because it needs to be an array formula.

You need to click in the cells that contain your formulas and press the keys: Ctrl Shift Enter

When you do that Excel puts a { } around the formula and so the formula becomes an array formula. When it becomes an array formula it enables Excel to know it needs to do a complex task. You can't type in the { } it can only be done by pressing Ctrl Shift and then Enter key.

You can find out more about array formulas in this Microsoft support page: https://support.office.com/en-us/ar...formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7

When you've turned the formula into an array formula you can then drag the formula down to apply the array formula to the rest of the rows like the screen shot below. Or alternatively you can use a specific cell like F2 below where you can enter the customer ID you want to check the dates for.

I coloured some of the cells to make it clearer to read. I selected G2 so you can see the array formula. And, I'm in the UK so the date format is set to UK date format for all of the cells containing dates. Like Eric says, the array formula needs to also be applied to the =MIN(IF

 

[MARK858]

@SpreadsheetGirl, What happens to your date column if you select the column then go to the Data tab, Text to columns, click next twice, check the Date checkbox and select your date format then click finish?

If it comes up correctly does the formula work now (either the array entered formula or the formula from post 5).

Btw you should never format a date as text, it is a number so if you want to see its value format it as a number.

 

[SpreadsheetGirl]

Hi guys,

thanks to both Dodo_Bodo and Mark858, both suggestions have worked. I now have the majority of customer IDs displaying the correct first and last order dates. There is about 30% of IDs though where I get 00/01/1900 as a result for both first and last order date. Any idea why this may be?

Thanks a lot for your help!