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!

 

[Tetra201]

Is this what you need?

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

 

[schistkicker]

No, I need both the left and right value on either side of "/" to be displayed in the E column. For example, if E3 has a value of 180 and I enter '1000/800 in D4, the resulting expression in E4 should be 820/620. Right now, if I enter as in the previous sentence I get the #VALUE! error return. However, if I modify to make the first value entry (1000) 3 characters instead of 4 (change 1000 to 999) it works fine and does what it is supposed to do: it spits out 819/620.

 

[Tetra201]

No, I need both the left and right value on either side of "/" to be displayed in the E column. For example, if E3 has a value of 180 and I enter '1000/800 in D4, the resulting expression in E4 should be 820/620...

What do you mean by "No"? -- You do not need a formula that does exactly what you want and returns "820/620"?

 

[schistkicker]

Sorry, I was answering your question "Is this what you need?". What I do need is to either fix the current function string to work with any number of characters to the left and right of the "/" (shouldn't exceed 4 characters or be less than 2) or I need a new function string that satisfies this required output based on the entry. Sorry if that was not clear.

 

[Tetra201]

Sorry, I was answering your question "Is this what you need?". What I do need is to either fix the current function string to work with any number of characters to the left and right of the "/" (shouldn't exceed 4 characters or be less than 2) or I need a new function string that satisfies this required output based on the entry. Sorry if that was not clear.

To make it short: have you tried putting the formula from Post #2 in cell E4 before replying?

By the way, your original formula works with any number of digits -- if the number of digits to the left of the "/" is the same as the number of digits to the right of it.

 

[HackSlash]

So I set up my sheet like you described and copied in your formula and I don't get #VALUE when I put a large number in. There might be a problem somewhere else on your sheet. What I DID notice is that it was truncating the data on the right half of the "/" because you have a "-1" on it. I changed that to "+1" so you minus one cell on the left and add one on the right. It appears to work in my test sheet.

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

If you need to see where the "#VALUE" is occurring you need to evaluate your formula and step through it to find the problem.

This will help you:

https://support.office.com/en-us/ar...t-a-time-59a201ae-d1dc-4b15-8586-a70aa409b8a7

 

[schistkicker]

Tetra201,
I apologize as I had mistakenly entered the function string into the cell exhibiting the problem (previously entered data further down the column) w/o applying the change to cell location per your new formula. Once I fixed this dumb mistake on my part, your revised function string works perfectly. Thank you. Of note is something interesting I figured out though based on your previous reply, you stated, "your original formula works with any number of digits -- if the number of digits to the left of the "/" is the same as the number of digits to the right of it." made me think a bit and the original function works fine so long as the number of characters before and after the / are identical (3/3, 4/4, etc.). My original issue could have been corrected by inserting 0 in front of 3 characters (0800) when the first entry was 4 characters (1000).

Thanks so much for your help, sorry for mis-entering your first response.

 

[schistkicker]

HackSlash,
Thank you for taking the time to reply. Unfortunately, I still get the same problem with a +1 versus a -1 on the right side for some reason. Probably have something not quite right in my sheet, this has been a hand-me-down file for a couple of years now. I will need to look into this a bit more to investigate the #VALUE! error link you've provided so I can learn how to troubleshoot my function entries. Tetra201's original revised function using the MID option solved the problem right away as soon as I entered the cell information correctly upon review.

I knew this page was the right place to ask my question, thanks to all that responded and helped work through this problem.

 

[Tetra201]

Thanks so much for your help

You are welcome.

My original issue could have been corrected by inserting 0 in front of 3 character

That would work, too. Probably too labor-intense, though.