x_fin nested if

Hi ,
I have been provided a file, which has latest version, but i am using 2010.. So i am getting #name error and the formula ifs are converted to xlfin .. the row lines go to 3000.. is there any code for changing this xlfin to normal ifs... i tried changing for every cell, it is tiring as the formulas are different..

Originally Posted by PaulM100

IFS will not work with 2010, so I think that you need to go to nested IFs. To replace in mass, just select the range and Ctrl+H, first box xlfin, second one ifs, replace all.

Hi,
It is giving #name

but how to do it for 2 sheets fully.. Is there any array function that can be created and used..
Doing each cell, i would complete after 30 days..

I tried to create a function using left,mid to insert if.. but that goes haywire... not working..

I'm not sure I can help but can you post some examples of the formula and also the result as expected?

Hi Akuini,
+_xlfn.IFS(D24=0,0,D24=1,+'account_type_transactions (2)'!AD19*E24,D24=2,+'account_type_transactions (2)'!AD19*E24+'account_type_transactions (2)'!AE19*E24)
+_xlfn.IFS(D24=0,0,D24=1,+'account_type_transactions (2)'!AD19*E24,D24=2,+'account_type_transactions (2)'!AD19*E24+'account_type_transactions (2)'!AE19*E24,D24=3,'account_type_transactions (2)'!AD19*E24+'account_type_transactions (2)'!AE19*E24+'account_type_transactions (2)'!AF19*E24)

some of the formulas, here you can see all formulas have different nested if count, so i am not understanding how to go about this...

Hi Mokko,
I dont know, why but the file is being upload failed.. Initially it was over mb and i compressed it, both failed.. I even tried to upload the sheet saved to another.. but keep failing..
i am not getting what is the issue..

Please show us the expected results of those examples.

Hi,
it is a nested if function, due to which when working below excel 2016 , it creates a x_fin at the begining..

So the correct formula for 2007/10/13 excel be like if(d24=0,0,if(d24=1,sheetname ad19*e24,if(d24=2.....
it goes on like this..
So in latest version this is replaced by IFS function, which is not available in older version. So i get X_fin at the beginning..
This i want to change to if(..if( as mentioned above..
is there any chance..

i tried capturing comma and after comma to insert.. But this always not give correct answer.. Manually changing is also not possible. Two sheets of 200 rows exist..
Any idea

Is it possible that your formula with IFS is just a part of more complex formula?
If the IFS formula is the whole formula and there is only 1 IFS, let's try this:
1. select 1 cell that has formula with IFS
2. run this code

see if works as expected, if it does then:
- I can amend the code so you can select multiple cells at once
- and we can explore how to deal with more complex formula.

Note: At the moment I put the result only in the immediate window, so you won't see any changes in the cell formula yet.

Please Login or Register  to view this content.

Result in Immediate Window:

Please Login or Register  to view this content.

Sorry, forgot to replace "_xlfn.IFS" with "IF", use this one:

Please Login or Register  to view this content.

OK, try tyhis:
Select the range then run "Sub IFS_to_IF":

Please Login or Register  to view this content.

Hi Akuini,
This is what i expected thank you.
I want to know, for if this is perfect. I dont have in my file anyother than if.. But there are other functions too upgraded in latest like max, min..
in that case you will get xlfn_max , so one has to change to max(if..(if .. In that case how to do it.

But there are other functions too upgraded in latest like max, min..
in that case you will get xlfn_max , so one has to change to max(if..(if .. In that case how to do it.

I'm not sure I can amend the code to do that, but can you give me some clear & simple examples and also the result as expected?

I think he means MAXIF and MAXIFS.

You might end up doing one for CONCAT and TEXTJOIN ...

Pete

Hi Akuini,
This is the formula
xlfn_max (D3:D15,C3:C15,"Finance")


Hi Pete,
Yes, in excel 2010 when i get latest version files, i have this xlfn issue.. So have to rechange full sheet.
Thats Y..

So it's only xlfn_max not xlfn_maxifs? what is the expected result?
Are there other functions besides xlfn_max? If yes, give us examples of each & also the results as expected.

Hi Akuini,
no i was wrong.. _xlfn.MaxIFS ... not max..
I dont know how to incorporate into lower version.. But the output is if the word finance in col C range then find max of col D range..
How to convert this to excel 2010.. I am not sure.. Do you have any idea..

How to convert this to excel 2010.. I am not sure.. Do you have any idea..

My knowledge of excel formula is very basic, so I don't know how.
But it would help if you can attach your file.
To attach a file:
In the reply box click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
(Don’t use the paper clip icon to attach your file, it doesn’t work.)

Note: I can see AliGW has provided you an answer, so maybe you can show some other formula examples (maybe more complex ones), so she or someone else can help you.

Hi Akuini,
If the AliGW's array formula is used, then how to change the cells. Previously we needed to add, only if, but right now it is totally different to the original formula?
I am trying to find any file in my office has this kind of formula, i dont have in my file. But i want to make sure, if i get again like this, to be more prepared. As the previous was very useful to change .. with this output and the previous formula sample, is it possible to change using macro?

Well, it's possible to do it with macro, but depends on the complexity it maybe hard to code.
It would be better to wait until you have the files in question, then you can start a new thread to post the new formulas problem.

And if the solution offered here so far answers your original question, then you can mark this thread as solved.