Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Value Plus ( or Minus) Upper (or Lower) Limit
Hello,
I ' trying to find a formula for the following situation. I have 2 adjacent intervals (length, in meters). Each interval has an upper and a lower limit. Each interval has it's own row and each of these rows has it's corresponding upper and lower limits, each in it's own cell. Every day work progress is uploaded into a table. This work progress gives the start and end points of the daily work. One day work can take place only in Interval 1 or 2; the following day it might start inside Interval 2 and end in Interval 1; and the day after it it might start in Interval 2 and end in Interval 1 (the opposite of the previous case). I need a formula that takes the daily start and end points and calculate how much distance was covered for each interval. For example: Interval 1: from 223 to 347 meters. Interval 2: from 347 to 510 meters. Daily Progress for today: from 329 to 419 meters. I need a formula that does the following calculations: Length of progress in Interval 1 = 347 (upper limit of Interval 1) - 329 (lower limit for daily progress) = 18 meters Length of progress in Interval 2 = 419 (upper limit for daily progress) - 347 (lower limit for Interval 2) = 72 meters And I need to take into consideration what I mentioned previously: - One day work can take place only in Interval 1 or 2; - The following day it might start inside Interval 2 and end in Interval 1; - The day after it it might start in Interval 2 and end in Interval 1. Thank you very much for all your help! -- igor |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Value Plus ( or Minus) Upper (or Lower) Limit
A9:B9 contain the endpoints of interval 1
A10:B10 contain the endpoints of interval 2 A12:B12 contain the endpoints of work accomplished today. In C12, calculate the portion of work that falls within interval 1: =SUMPRODUCT((ROW(INDIRECT(A12&":"&B12))=$A$9)*(RO W(INDIRECT (A12&":"&B12))<$B$9)) In D12, calculate the portion of work that falls within interval 2: =SUMPRODUCT((ROW(INDIRECT(A12&":"&B12))=$A$10)*(R OW(INDIRECT (A12&":"&B12))<$B$10)) If other endpoints are in A:B below row 12 you can copy the formula down. HTH Kostis Vezerides On Nov 13, 7:04*pm, Igorin wrote: Hello, I ' trying to find a formula for the following situation. I have 2 adjacent intervals (length, in meters). Each interval has an upper and a lower limit. Each interval has it's own row and each of these rows has it's corresponding upper and lower limits, each in it's own cell. Every day work progress is uploaded into a table. This work progress gives the start and end points of the daily work. One day work can take place only in Interval 1 or 2; the following day it might start inside Interval 2 and end in Interval 1; and the day after it it might start in Interval 2 and end in Interval 1 (the opposite of the previous case). I need a formula that takes the daily start and end points and calculate how much distance was covered for each interval. For example: Interval 1: from 223 to 347 meters. Interval 2: from 347 to 510 meters. Daily Progress for today: from 329 to 419 meters. I need a formula that does the following calculations: Length of progress in Interval 1 = 347 (upper limit of Interval 1) - 329 (lower limit for daily progress) = 18 meters Length of progress in Interval 2 = 419 (upper limit for daily progress) - 347 (lower limit for Interval 2) = 72 meters And I need to take into consideration what I mentioned previously: - One day work can take place only in Interval 1 or 2; - The following day it might start inside Interval 2 and end in Interval 1; - The day after it it might start in Interval 2 and end in Interval 1. Thank you very much for all your help! -- igor |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Value Plus ( or Minus) Upper (or Lower) Limit
Hello, Vezerid!
Thank you for taking the time to help. The formula you sugest returns a formula error. Do you have an idea of what the cause might be? -- igor "vezerid" wrote: A9:B9 contain the endpoints of interval 1 A10:B10 contain the endpoints of interval 2 A12:B12 contain the endpoints of work accomplished today. In C12, calculate the portion of work that falls within interval 1: =SUMPRODUCT((ROW(INDIRECT(A12&":"&B12))=$A$9)*(RO W(INDIRECT (A12&":"&B12))<$B$9)) In D12, calculate the portion of work that falls within interval 2: =SUMPRODUCT((ROW(INDIRECT(A12&":"&B12))=$A$10)*(R OW(INDIRECT (A12&":"&B12))<$B$10)) If other endpoints are in A:B below row 12 you can copy the formula down. HTH Kostis Vezerides On Nov 13, 7:04 pm, Igorin wrote: Hello, I ' trying to find a formula for the following situation. I have 2 adjacent intervals (length, in meters). Each interval has an upper and a lower limit. Each interval has it's own row and each of these rows has it's corresponding upper and lower limits, each in it's own cell. Every day work progress is uploaded into a table. This work progress gives the start and end points of the daily work. One day work can take place only in Interval 1 or 2; the following day it might start inside Interval 2 and end in Interval 1; and the day after it it might start in Interval 2 and end in Interval 1 (the opposite of the previous case). I need a formula that takes the daily start and end points and calculate how much distance was covered for each interval. For example: Interval 1: from 223 to 347 meters. Interval 2: from 347 to 510 meters. Daily Progress for today: from 329 to 419 meters. I need a formula that does the following calculations: Length of progress in Interval 1 = 347 (upper limit of Interval 1) - 329 (lower limit for daily progress) = 18 meters Length of progress in Interval 2 = 419 (upper limit for daily progress) - 347 (lower limit for Interval 2) = 72 meters And I need to take into consideration what I mentioned previously: - One day work can take place only in Interval 1 or 2; - The following day it might start inside Interval 2 and end in Interval 1; - The day after it it might start in Interval 2 and end in Interval 1. Thank you very much for all your help! -- igor |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Value Plus ( or Minus) Upper (or Lower) Limit
Answered in your other post. -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=29155 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Value Plus ( or Minus) Upper (or Lower) Limit
Sorry, when I copied it I left a space inbetween. That was the cause of the
error. It works now! thank you very much for the help!!! -- igor "vezerid" wrote: A9:B9 contain the endpoints of interval 1 A10:B10 contain the endpoints of interval 2 A12:B12 contain the endpoints of work accomplished today. In C12, calculate the portion of work that falls within interval 1: =SUMPRODUCT((ROW(INDIRECT(A12&":"&B12))=$A$9)*(RO W(INDIRECT (A12&":"&B12))<$B$9)) In D12, calculate the portion of work that falls within interval 2: =SUMPRODUCT((ROW(INDIRECT(A12&":"&B12))=$A$10)*(R OW(INDIRECT (A12&":"&B12))<$B$10)) If other endpoints are in A:B below row 12 you can copy the formula down. HTH Kostis Vezerides On Nov 13, 7:04 pm, Igorin wrote: Hello, I ' trying to find a formula for the following situation. I have 2 adjacent intervals (length, in meters). Each interval has an upper and a lower limit. Each interval has it's own row and each of these rows has it's corresponding upper and lower limits, each in it's own cell. Every day work progress is uploaded into a table. This work progress gives the start and end points of the daily work. One day work can take place only in Interval 1 or 2; the following day it might start inside Interval 2 and end in Interval 1; and the day after it it might start in Interval 2 and end in Interval 1 (the opposite of the previous case). I need a formula that takes the daily start and end points and calculate how much distance was covered for each interval. For example: Interval 1: from 223 to 347 meters. Interval 2: from 347 to 510 meters. Daily Progress for today: from 329 to 419 meters. I need a formula that does the following calculations: Length of progress in Interval 1 = 347 (upper limit of Interval 1) - 329 (lower limit for daily progress) = 18 meters Length of progress in Interval 2 = 419 (upper limit for daily progress) - 347 (lower limit for Interval 2) = 72 meters And I need to take into consideration what I mentioned previously: - One day work can take place only in Interval 1 or 2; - The following day it might start inside Interval 2 and end in Interval 1; - The day after it it might start in Interval 2 and end in Interval 1. Thank you very much for all your help! -- igor |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Value Plus ( or Minus) Upper (or Lower) Limit
Thank you very much for taking the time to help!
I'll take a look a it as soon as I have enough time to give it the attention it deserves. Thanks again! -- igor "shg" wrote: Answered in your other post. -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=29155 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Value Plus ( or Minus) Upper (or Lower) Limit
Hello,
I'm not getting the correct results. I just verified manually and the results are not correct. I'm using the formula sugested by shg. Thank you anyway for taking the time to help! -- igor "vezerid" wrote: A9:B9 contain the endpoints of interval 1 A10:B10 contain the endpoints of interval 2 A12:B12 contain the endpoints of work accomplished today. In C12, calculate the portion of work that falls within interval 1: =SUMPRODUCT((ROW(INDIRECT(A12&":"&B12))=$A$9)*(RO W(INDIRECT (A12&":"&B12))<$B$9)) In D12, calculate the portion of work that falls within interval 2: =SUMPRODUCT((ROW(INDIRECT(A12&":"&B12))=$A$10)*(R OW(INDIRECT (A12&":"&B12))<$B$10)) If other endpoints are in A:B below row 12 you can copy the formula down. HTH Kostis Vezerides On Nov 13, 7:04 pm, Igorin wrote: Hello, I ' trying to find a formula for the following situation. I have 2 adjacent intervals (length, in meters). Each interval has an upper and a lower limit. Each interval has it's own row and each of these rows has it's corresponding upper and lower limits, each in it's own cell. Every day work progress is uploaded into a table. This work progress gives the start and end points of the daily work. One day work can take place only in Interval 1 or 2; the following day it might start inside Interval 2 and end in Interval 1; and the day after it it might start in Interval 2 and end in Interval 1 (the opposite of the previous case). I need a formula that takes the daily start and end points and calculate how much distance was covered for each interval. For example: Interval 1: from 223 to 347 meters. Interval 2: from 347 to 510 meters. Daily Progress for today: from 329 to 419 meters. I need a formula that does the following calculations: Length of progress in Interval 1 = 347 (upper limit of Interval 1) - 329 (lower limit for daily progress) = 18 meters Length of progress in Interval 2 = 419 (upper limit for daily progress) - 347 (lower limit for Interval 2) = 72 meters And I need to take into consideration what I mentioned previously: - One day work can take place only in Interval 1 or 2; - The following day it might start inside Interval 2 and end in Interval 1; - The day after it it might start in Interval 2 and end in Interval 1. Thank you very much for all your help! -- igor |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert lower to upper case automatically without using UPPER | Excel Discussion (Misc queries) | |||
create SPC chart with 3 sigma Upper/Lower control limit | Charts and Charting in Excel | |||
Change from mixed caps and upper lower to all upper lower case | Excel Worksheet Functions | |||
Count occurence with user defined upper&lower limit. | Excel Worksheet Functions | |||
How do I convert all upper case excel sheet into upper and lower . | Excel Discussion (Misc queries) |