schistkicker
New Member
- Joined
- Jan 6, 2016
- Messages
- 9
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:
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!
- Cell E3 has the unchanging numerical value
- Column D (row 4 to infinity) has the entries that are the initial values to deduct E3
- 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!