Help with Function String Syntax

[schistkicker]

Hello Excel experts, I hope someone on this forum can assist with fixing a function string that works 90% of the time but has two major issues that come up when uncommon values are entered. in the first cell, we have two numerical values separated by a forward slash ("/"), the adjacent has the column has the function string which is to deduct a value that does not change throughout the entire sheet. Here is how this sheet is setup:

1. Cell E3 has the unchanging numerical value
2. Column D (row 4 to infinity) has the entries that are the initial values to deduct E3
3. Column E (row 4 to infinity) has the function string that determines how to deduct E3 from Dx

Here is the function string:

=IF(COUNTIF(D4,"*/*"),(LEFT(D4,FIND("/",D4,1)-1)-$E$3&"/"&RIGHT(D4,FIND("/",D4,1)-1)-$E$3),(D4-$E$3))

The function works great when the values entered are 3 or less characters (500/400 or 50/40), but if this deviates to 4 (5000/4000) characters the result is #VALUE!.

I'm not sure if you need additional information, but I could provide if needed. If there is a better way to make the function string work or if the method of entry is what is causing the issue, please help and provide recommendations.

Thank you again!

 

[Rick Rothstein]

I believe this slightly "simpler" formula will also work...

=IFERROR(LEFT(D4,FIND("/",D4)-1)-E$3&"/"&MID(D4,FIND("/",D4)+1,99)-E$3,D4-E$3)

 

[schistkicker]

Rick, this simpler version works too. Thank you for providing an additional option to consider when updating our master sheet. Again, thanks to everyone that helped me get this fixed!

 

[mikerickson]

If you are updating a master sheet, your world would be a lot simpler if you got added columns so that each number could have its own column and got rid of the whole backslash, two-data-values-in-the-same-cell problem.

 

[schistkicker]

mikerickson,
Unfortunately, it is not this simple and if your solution was an option I would not have consulted this board for help. When entering this data, I have either one number and a respective output of one number, or a two number input with respective teo number output. The purpose of the function is to discern if there is one versus two numbers entered, and the function's purpose is to output matches that match the respective number of values input (separated by some character in between as a wildcard, in this case a "/"). We are recording pressure values in our field work and the recording of one or two values is a fingerprint for a certain expected behavior, this needs to be displayed for technical discussion during the repose to our clients. To have four columns in place of the original two would involve additional data entry for values with one digit (an n/a or something similar in the second number's column) that would potentially double the data entry time.