Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data ranges within a column
Range D15 through D830 contains numbers of various numeric values. In column
F15 I want to report the total number of entries in D15 through D830 that fall within certain parameters, ie...all of the numbers that fall between 1 and 999. In F15 I want to report all ov the numbers that fall between 1000 and 2499, etc.... Is this a VLOOKUP function and how do I arrange it to properly report? Thanks! Jack |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data ranges within a column
Try this:
F15: =SUMPRODUCT((D15:D830=1)*(D15:D830<=999)) F16: =SUMPRODUCT((D15:D830=1000)*(D15:D830<=2499)) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "JT" wrote: Range D15 through D830 contains numbers of various numeric values. In column F15 I want to report the total number of entries in D15 through D830 that fall within certain parameters, ie...all of the numbers that fall between 1 and 999. In F15 I want to report all ov the numbers that fall between 1000 and 2499, etc.... Is this a VLOOKUP function and how do I arrange it to properly report? Thanks! Jack |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data ranges within a column
Ron:
It worked just fine! Many thanks! I did come up with one more question on a different subject, though. In d15 I have a numeric value that indicates a donated dollar figure that will be paid out over either 3, 4 or 5 years. Column c15 indicates the number of years.I n e15, F15, G15, H15 and I15 I want to show the dollar figure that will be paid in that year. For example: if the donor donates 1000 and chooses a payout of 4 years, I want e15, f15, g15 and H15 to show the amount due that year...with a "0" in I15. Same for a 3 year payout or a 5 year payout. Can you help me again? Jack If the "Ron Coderre" wrote: Try this: F15: =SUMPRODUCT((D15:D830=1)*(D15:D830<=999)) F16: =SUMPRODUCT((D15:D830=1000)*(D15:D830<=2499)) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "JT" wrote: Range D15 through D830 contains numbers of various numeric values. In column F15 I want to report the total number of entries in D15 through D830 that fall within certain parameters, ie...all of the numbers that fall between 1 and 999. In F15 I want to report all ov the numbers that fall between 1000 and 2499, etc.... Is this a VLOOKUP function and how do I arrange it to properly report? Thanks! Jack |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data ranges within a column
Try this:
E15: =IF($C15COLUMNS($D$1:D$1),TRUNC($D15/$C15),IF($C15=COLUMNS($D$1:D$1),$D15-TRUNC(($C15-1)*$D15/$C15),"")) Copy that formula across thru I15 and down as far as you need. Note: that formula rounds down in the first months of the allocation and compensates in the last month. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "JT" wrote: Ron: It worked just fine! Many thanks! I did come up with one more question on a different subject, though. In d15 I have a numeric value that indicates a donated dollar figure that will be paid out over either 3, 4 or 5 years. Column c15 indicates the number of years.I n e15, F15, G15, H15 and I15 I want to show the dollar figure that will be paid in that year. For example: if the donor donates 1000 and chooses a payout of 4 years, I want e15, f15, g15 and H15 to show the amount due that year...with a "0" in I15. Same for a 3 year payout or a 5 year payout. Can you help me again? Jack If the "Ron Coderre" wrote: Try this: F15: =SUMPRODUCT((D15:D830=1)*(D15:D830<=999)) F16: =SUMPRODUCT((D15:D830=1000)*(D15:D830<=2499)) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "JT" wrote: Range D15 through D830 contains numbers of various numeric values. In column F15 I want to report the total number of entries in D15 through D830 that fall within certain parameters, ie...all of the numbers that fall between 1 and 999. In F15 I want to report all ov the numbers that fall between 1000 and 2499, etc.... Is this a VLOOKUP function and how do I arrange it to properly report? Thanks! Jack |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data ranges within a column
Very helpful, Ron! Thanks again!
JT "Ron Coderre" wrote: Try this: E15: =IF($C15COLUMNS($D$1:D$1),TRUNC($D15/$C15),IF($C15=COLUMNS($D$1:D$1),$D15-TRUNC(($C15-1)*$D15/$C15),"")) Copy that formula across thru I15 and down as far as you need. Note: that formula rounds down in the first months of the allocation and compensates in the last month. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "JT" wrote: Ron: It worked just fine! Many thanks! I did come up with one more question on a different subject, though. In d15 I have a numeric value that indicates a donated dollar figure that will be paid out over either 3, 4 or 5 years. Column c15 indicates the number of years.I n e15, F15, G15, H15 and I15 I want to show the dollar figure that will be paid in that year. For example: if the donor donates 1000 and chooses a payout of 4 years, I want e15, f15, g15 and H15 to show the amount due that year...with a "0" in I15. Same for a 3 year payout or a 5 year payout. Can you help me again? Jack If the "Ron Coderre" wrote: Try this: F15: =SUMPRODUCT((D15:D830=1)*(D15:D830<=999)) F16: =SUMPRODUCT((D15:D830=1000)*(D15:D830<=2499)) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "JT" wrote: Range D15 through D830 contains numbers of various numeric values. In column F15 I want to report the total number of entries in D15 through D830 that fall within certain parameters, ie...all of the numbers that fall between 1 and 999. In F15 I want to report all ov the numbers that fall between 1000 and 2499, etc.... Is this a VLOOKUP function and how do I arrange it to properly report? Thanks! Jack |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
Loading Column Data with blank Rows into Data Validation Box | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions |