Minifs/Maxifs and the new formulas working on new excel file but doesn't work on older excel files

netanel99

Board Regular
Joined
May 13, 2013
Messages
162
hello amazing people,

thanks for all the help, you guys are always great, wanted to ask you the question on the title, both excel files- the new one and the older one is in .xlsm and I open both with office 2019 pro - how can I make the older file get the new formulas like if I open new excel file.

the formulas that doesn't work with the older excel file are - IFS Function, MINIFS And MAXIFS Function and the other new stuff added recently for office 2019 and 365..

thanks for any sort of help ,
Netanel
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
798
Office Version
  1. 365
Platform
  1. Windows
You don't by any chance have 2 versions of Excel installed do you ?
If I open an xls file and without converting it to xlsx or xlsm I have access to all those functions in the spreadsheet. I am using Office 365.

1614423949587.png
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,914
Office Version
  1. 365
Platform
  1. Windows
You don't by any chance have 2 versions of Excel installed do you ?
This could be possible, although I'm not entirely sure how you choose which version is used, when I installed a new version without uninstalling the old one first I found that only the old one would open.

With regard to the missing functions, you would have everything available that is included with the version used to open the file, not the version used to create it.
With the exception of vba in a macro free workbook, the file type should not make any difference as long as it is a valid excel file type.
 

netanel99

Board Regular
Joined
May 13, 2013
Messages
162
you guys are always amazing , genius people as always, I noticed that if I open EXCEL 2019 from start menu then it let me use MAXIF and the other formulas, then I can open the file manually and everything is right, but if I open the file directly then it opens it in excel 2019 also , but doesn't let me have those formulas, It seems weird to me , still don't understand what excatly the problem , maybe I need to check if I have 2 excel 2019 installed ? weird. lol
 

netanel99

Board Regular
Joined
May 13, 2013
Messages
162

ADVERTISEMENT

ahhh.. it's so weird, when I open the excel file from start menu I can use MAXIFS on new file or if I open my older file,
If I open directly the older file it open with excel 2019 and it doesn't have MAXIFS Etc on it,
and after I located the excel location from start menu where it works good, and right click on the older file and assign it to always open from that location , it still doesn't work,
so frustrating, any help with a mystery like this one ?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,914
Office Version
  1. 365
Platform
  1. Windows
Try both methods of opening and check the version / build references to see if they are 2 different versions (i.e. check one that has maxifs and one where it is missing).

Go to File then Account and look to the lower right area where it says 'About Excel', you will find version and build numbers there.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,158
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: https://www.excelforum.com/excel-fo...file-but-doesnt-work-on-past-excel-files.html
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

netanel99

Board Regular
Joined
May 13, 2013
Messages
162
Sorry MR, I have only one forum I questioned there, in diff words but still kinda the same, I tried delete there but I can't so I put it on SOLVED there hope it's ok , won't happen again.

thanks

jasonb75,​

did it , it's the same beta file on both occasions, anigma, frustrating .
also I never open it from compability mode, I rechecked it.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,914
Office Version
  1. 365
Platform
  1. Windows
it's the same beta file on both occasions,
'beta' implies a preview build which could potentially be unstable, not sure why you would have it with 2019 though, I thought only 365 users got beta versions if they are on the office insider program.

When you say that ' it doesn't let you have those formulas', where are you looking? Are you typing the formula into an empty cell? If so, what happens when you hit enter?
 

netanel99

Board Regular
Joined
May 13, 2013
Messages
162
you'r right I meant version, the two, weirdingly enough, are the same.
yes, that's right I type the formula on an empty cell, when I open the excel from start menu it gives me recognision and I can use the Minifs formula for instance,
but when I open .xlsm file , which I created one year ago , and it opens also with the same version of excel 2019 pro when I type it it doesn't recognise it, and also I can't use the formula already typed there, it's just gives nothing like I've invented it or something :) seriously you guys are my only option to understand what happening it's just weird to me.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,706
Messages
5,654,837
Members
418,156
Latest member
juliapearson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top