Calculating a distance within an interval in a cell with text
Closed
ohai309
Posts
2
Registration date
Wednesday August 20, 2014
Status
Member
Last seen
August 21, 2014
-
Aug 20, 2014 at 10:56 PM
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - Aug 22, 2014 at 03:37 PM
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - Aug 22, 2014 at 03:37 PM
Related:
- Which excel function uses the distance from one cell to another?
- Transfer data from one excel worksheet to another automatically - Guide
- Number to words in excel - Guide
- Excel send value to another cell - Guide
- Excel date format dd.mm.yyyy - Guide
- How to take screenshot in excel - Guide
3 responses
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Aug 21, 2014 at 07:14 PM
Aug 21, 2014 at 07:14 PM
ohai309, Good evening.
Sorry, but it's a little confused to understand.
Please, could you give us some examples about the data you expect to receive as result?
Sorry, but it's a little confused to understand.
Please, could you give us some examples about the data you expect to receive as result?
ohai309
Posts
2
Registration date
Wednesday August 20, 2014
Status
Member
Last seen
August 21, 2014
Aug 21, 2014 at 10:22 PM
Aug 21, 2014 at 10:22 PM
Hi Mazzaroi,
I have a spreadsheet containing data for sanitary sewers. For each sewer, I have a cell that gives a recommendation for repairing that segment. I want to automate my spreadsheet such that I can type in a rehabilitation measure and another cell will spit out the quantity that needs to be repaired (so I can multiply it by an already determined unit price). For example, my recommendation might be the following:
Case 1: One recommendation, one interval
Grout (5'-21')
Meaning I want to put grout starting at 5' in the sewer and ending at 21' in the sewer. If I can pull out the quantity to grout from that format, I'll be good. The result for this case would be 16.
Case 2: One recommendation, multiple intervals
Grout (5'-21', 68'-102')
Meaning I want to grout over two separate intervals. I'd want to pull out the total footage to grout for that segment, making the result be (21-5)+(102-68)= 50
Case 3: Two recommendations, one interval each
Grout (5'-21'); Line (30'-40')
Meaning I want to grout for one interval and put the quantity to grout in one cell, and line over another interval and put that quantity in another cell. The cell in my "Grout" column would say 16 and the cell in my "Line" column would say 10.
And so on and so forth with various combinations with those three cases.
I have a spreadsheet containing data for sanitary sewers. For each sewer, I have a cell that gives a recommendation for repairing that segment. I want to automate my spreadsheet such that I can type in a rehabilitation measure and another cell will spit out the quantity that needs to be repaired (so I can multiply it by an already determined unit price). For example, my recommendation might be the following:
Case 1: One recommendation, one interval
Grout (5'-21')
Meaning I want to put grout starting at 5' in the sewer and ending at 21' in the sewer. If I can pull out the quantity to grout from that format, I'll be good. The result for this case would be 16.
Case 2: One recommendation, multiple intervals
Grout (5'-21', 68'-102')
Meaning I want to grout over two separate intervals. I'd want to pull out the total footage to grout for that segment, making the result be (21-5)+(102-68)= 50
Case 3: Two recommendations, one interval each
Grout (5'-21'); Line (30'-40')
Meaning I want to grout for one interval and put the quantity to grout in one cell, and line over another interval and put that quantity in another cell. The cell in my "Grout" column would say 16 and the cell in my "Line" column would say 10.
And so on and so forth with various combinations with those three cases.
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Aug 22, 2014 at 03:37 PM
Aug 22, 2014 at 03:37 PM
ohai309, Good afternoon.
I don't know why you need to use one cell to describe a complex Text-Number string as your technical recommendation. Maybe you have no other choice.
You could use some separate columns to create your recommendation.
Then the excel formulas would work faster and in easier way.
If, for some reason, after using some separated columns for your calculus, you need to have this unique Text-Number cell, maybe in a report, is possible to using a formula to form it.
"...And so on and so forth with various combinations with those three cases...."
I think that for this kind of situation, without fixed standards, you'll need a VBA Programming.
It's always more flexible.
I don't know why you need to use one cell to describe a complex Text-Number string as your technical recommendation. Maybe you have no other choice.
You could use some separate columns to create your recommendation.
Then the excel formulas would work faster and in easier way.
If, for some reason, after using some separated columns for your calculus, you need to have this unique Text-Number cell, maybe in a report, is possible to using a formula to form it.
"...And so on and so forth with various combinations with those three cases...."
I think that for this kind of situation, without fixed standards, you'll need a VBA Programming.
It's always more flexible.
