| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range Name Use
I was reviewing a formula developed by a fellow employee and cannot figure
out WHY it works. =(range1)*0.1 facts: range1 refers to sheet1!$g$2:$g$27 dates in sheet1!b2:b27 are the same as sheet2!b2:b27 formula above is in sheet2 cells c2 to c27 Why does the formula know to bring the value in range1 for the same date? I have rechecked and 1) the name range1 refers to a single column array of values and does not include the date and 2) the formual is not entered as an array formula. Thanks. Mike |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Range Name Use
It's not bringing the value for the same date. It's using the value for the
same row. If you were to switch 2 of the dates, the formulas would still calculate the same as they are now. If you want to ensure they calculate for the correct date, then your formula in C2 should be something like: =SUMIF(Sheet1!$B$2:$B$27,B2,range1)*0.01 -- ** John C ** "Mike S" wrote: I was reviewing a formula developed by a fellow employee and cannot figure out WHY it works. =(range1)*0.1 facts: range1 refers to sheet1!$g$2:$g$27 dates in sheet1!b2:b27 are the same as sheet2!b2:b27 formula above is in sheet2 cells c2 to c27 Why does the formula know to bring the value in range1 for the same date? I have rechecked and 1) the name range1 refers to a single column array of values and does not include the date and 2) the formual is not entered as an array formula. Thanks. Mike |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Range Name Use
Thanks for the quick reply John. Normally we vlookup on the date and bring
the value over that way. Mike "John C" wrote: It's not bringing the value for the same date. It's using the value for the same row. If you were to switch 2 of the dates, the formulas would still calculate the same as they are now. If you want to ensure they calculate for the correct date, then your formula in C2 should be something like: =SUMIF(Sheet1!$B$2:$B$27,B2,range1)*0.01 -- ** John C ** "Mike S" wrote: I was reviewing a formula developed by a fellow employee and cannot figure out WHY it works. =(range1)*0.1 facts: range1 refers to sheet1!$g$2:$g$27 dates in sheet1!b2:b27 are the same as sheet2!b2:b27 formula above is in sheet2 cells c2 to c27 Why does the formula know to bring the value in range1 for the same date? I have rechecked and 1) the name range1 refers to a single column array of values and does not include the date and 2) the formual is not entered as an array formula. Thanks. Mike |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Range Name Use
That's fine too, assuming you only have 1 date and 1 value per date. If you
had multiple values per date over several rows, though, the VLOOKUP will only bring over the first value. -- ** John C ** "Mike S" wrote: Thanks for the quick reply John. Normally we vlookup on the date and bring the value over that way. Mike "John C" wrote: It's not bringing the value for the same date. It's using the value for the same row. If you were to switch 2 of the dates, the formulas would still calculate the same as they are now. If you want to ensure they calculate for the correct date, then your formula in C2 should be something like: =SUMIF(Sheet1!$B$2:$B$27,B2,range1)*0.01 -- ** John C ** "Mike S" wrote: I was reviewing a formula developed by a fellow employee and cannot figure out WHY it works. =(range1)*0.1 facts: range1 refers to sheet1!$g$2:$g$27 dates in sheet1!b2:b27 are the same as sheet2!b2:b27 formula above is in sheet2 cells c2 to c27 Why does the formula know to bring the value in range1 for the same date? I have rechecked and 1) the name range1 refers to a single column array of values and does not include the date and 2) the formual is not entered as an array formula. Thanks. Mike |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Range Name Use
Only one value per date since the dates in question here are always the first
day of the month. Instances where there are more than one date within a month are summed using an array formula with month/year criteria. "John C" wrote: That's fine too, assuming you only have 1 date and 1 value per date. If you had multiple values per date over several rows, though, the VLOOKUP will only bring over the first value. -- ** John C ** "Mike S" wrote: Thanks for the quick reply John. Normally we vlookup on the date and bring the value over that way. Mike "John C" wrote: It's not bringing the value for the same date. It's using the value for the same row. If you were to switch 2 of the dates, the formulas would still calculate the same as they are now. If you want to ensure they calculate for the correct date, then your formula in C2 should be something like: =SUMIF(Sheet1!$B$2:$B$27,B2,range1)*0.01 -- ** John C ** "Mike S" wrote: I was reviewing a formula developed by a fellow employee and cannot figure out WHY it works. =(range1)*0.1 facts: range1 refers to sheet1!$g$2:$g$27 dates in sheet1!b2:b27 are the same as sheet2!b2:b27 formula above is in sheet2 cells c2 to c27 Why does the formula know to bring the value in range1 for the same date? I have rechecked and 1) the name range1 refers to a single column array of values and does not include the date and 2) the formual is not entered as an array formula. Thanks. Mike |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Range to echo format (hilite/fontcolors) of sister range? | Excel Worksheet Functions | |||
| Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
| Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
| formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) | |||
| How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
Linear Mode
