Excel - Not Using The MOREFUNC

[hlewton]

Hans, years ago, like around 2002, I used this formula in a cell and it gave me a dollar amount owed for those attending a skiing event my wife and I ran. To get it to give that dollar amount, I had to install an add on called “MOREFUNC.” I am sure I did not come up with this formula on my own nor did I know about the “MOREFUNC” on my own. This is the formula -
=IF(ISERROR(A82*$B$1-SUM(EVAL("{"&SUBSTITUTE(H82,CHAR(10),",")&"}"))),"",(A82*$B$1-SUM(EVAL("{"&SUBSTITUTE(H82,CHAR(10),",")&"}"))))-I82+20

I believe someone I used to work with initially created this formula and told me about the MOREFUNC, but that was before 2001.

My question is, as can be seen in the attachment, the column named “OWED” now shows “#VALUE” and not the actual number value I need. The MOREFUNC somehow made that cell show the actual value I needed it to show. I still have the MOREFUNC with, probably your instructions, for Excel 2010 32 Bit version. From all I can find, the MOREFUNC only works of 32 Bit version of Excel and not sure the latest version that even the 32 Bit version works on. I no longer use Excel 32 Bit, I have the 64 Bit version. Is there anything I can do to make the cells inside the RED box once again display the numeric value of the formula?

Thanks

H & H.jpg

[HansV]

Laurent Longre, the author of the Morefunc add-in, has long since abandoned it, and he has never released a 64-bit function.
What do H82 and similar cells contain?

[hlewton]

Laurent Longre, the author of the Morefunc add-in, has long since abandoned it, and he has never released a 64-bit function.
What do H82 and similar cells contain?

I will have to check that and get back to you.

[hlewton]

Please see the attachment. It seems to be the amount of maybe the first or down payment or a number such as $180.00.

Payment.jpg

[HansV]

Are there cells in column H that contain more than one value, each value on a separate line within the cell?

[hlewton]

Are there cells in column H that contain more than one value, each value on a separate line within the cell?

Yes. I found a few that had 2 entries and 1 that had 3 entries. I don't even remember why I did things like that back then but possibly it was to keep couples or small groups listed together in order to assign them to the same room once at the destination.

[HansV]

Try this:

=IFERROR(A82*$B$1-SUM(--TEXTSPLIT(H82, CHAR(10), , TRUE))-I82+20, "")

[hlewton]

Try this:

=IFERROR(A82*$B$1-SUM(--TEXTSPLIT(H82, CHAR(10), , TRUE))-I82+20, "")

I will try that as soon as I finish this post. I kept thinking and thinking about why there would be multiple entries in a cell. The price of the trip could be paid for in increments. So, the first payment was due by a certain date, same for second payment, and final payment. Of course it made it much easier if a person would pay the entire amount in 1 payment but that didn't happen for most who signed up for the trip. So, again thinking back, I probably explained to someone at work that I didn't want to stretch the spreadsheet out to so many columns and they suggest the solution I went with of having multiple entries in one cell and then using the MOREFUNC to do the job.

[hlewton]

I am not sure what is happening. I believe both columns H and I are used to determine what is OWED in column J. When I use your formula without making any other changes to the spreadsheet in columns H and I, column J shows 0 as would be expected. Then I tested it further by deleting the the values in columns H and I and the formula stills shows 0 which it shouldn't. Please see attachment.

payments.jpg

In addition, I am not sure what the "+20" is anymore. It's possible that person paid additional for something that most others didn't have to pay. I inadvertently picked a bad example for a cell in column J. So, I copied the first 3 rows of the formulas in column J and here they are:
=IF(ISERROR(A4*$B$1-SUM(EVAL("{"&SUBSTITUTE(H4,CHAR(10),",")&"}"))),"",(A4*$B$1-SUM(EVAL("{"&SUBSTITUTE(H4,CHAR(10),",")&"}"))))-I4

=IF(ISERROR(A5*$B$1-SUM(EVAL("{"&SUBSTITUTE(H5,CHAR(10),",")&"}"))),"",(A5*$B$1-SUM(EVAL("{"&SUBSTITUTE(H5,CHAR(10),",")&"}"))))-I5

=IF(ISERROR(A6*$B$1-SUM(EVAL("{"&SUBSTITUTE(H6,CHAR(10),",")&"}"))),"",(A6*$B$1-SUM(EVAL("{"&SUBSTITUTE(H6,CHAR(10),",")&"}"))))-I6
The end (-I4) etc. are capital i, that to me look like 1s

Hopefully that makes a difference by not always showing a 0 when using a new formula. So the first formula would go into cell J4 and then be copied into the remaining cells in column J.

[HansV]

Could you attach a copy of the workbook without sensitive info?

[hlewton]

Could you attach a copy of the workbook without sensitive info?

In trying to attach the Excel file, it said it was too big. It is 328 KB. Do I need to send it by email?

Here is the message I get when editing and re saving the file I get the message you can see in the attachment. I just clicked "Continue."

File Format.jpg

[HansV]

Try saving the workbook as a macro-enabled workbook (*.xlsm). The file size should be smaller.

[hlewton]

Hans, I am really sorry I don’t remember more about how I used this workbook, but I noticed there were blank Value entries on other sheets in this workbook. I’m sure, at the time, I had valid reasons for setting it up this way but I just don’t know what they were now. I do remember that year after year that I ran this trip I used the MOREFUNC and everywhere the word VALUE appears had an actual value.

It did come out a lot smaller so here it is.

Test 1 Holiday_Holimont2.xlsm

[HansV]

I have looked only at the Skiers sheet. I replaced the Eval function from MoreFunc with a TEXTSPLIT.
The original formulas were tweaked to make the result in all cells in J2:J85 equal to 0.

Test 1 Holiday_Holimont2.xlsm

[hlewton]

Thank you.

[hlewton]

Hans, I have tried unsuccessfully for a few days to use your formula as an example and fix the rest of the workbook. On sheet Paid Ski Club 9-26-02, I know the columns are slightly different from the Skiers sheet. On sheet Paid Ski Club 12-10-02, I believe it is setup exactly as the Skiers sheet is up to column K, but I can not get the formula such as “=A4*$B$1-SUM(--TEXTSPLIT(H4,CHAR(10),,TRUE))-I4,” which works perfectly in cell J4 on the Skiers worksheet to work correctly on the Paid Ski Club 12-10-02 work sheet. Shouldn’t that same formula work since the worksheets are set up the same? When I enter that formula in the cells in column J, all values are set to 0 even if I delete a payment, which should change the value from 0.I am not at all sure about anything working on Paid Ski Club 9-26-02 worksheet because it is not the same layout as the other 2 mentioned here.

[HansV]

In the attached workbook I have added the formulas in the first few rows on those two sheets. The cells are marked in yellow.
Please note that several of the cells below have manual additions such as +10 or -20, so you cannot just copy the formulas downwards.

Test 1 Holiday_Holimont2.xlsm

[hlewton]

OK, thank you.

[hlewton]

Hans, Thanks again. I believe I have the entire workbook edited thanks to you. That was one of the first years I used that type of workbook. I used it for about 8 years consecutively and the MOREFUNC always seemed to work perfectly for me. Too bad it isn't still available.