Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
subtotal based on ref no
Excel 2003 and XP
I have a spreadsheet that can't be changed so in a separate sheet I want to be able to create totals of variable ranges. Each range changes based on the reference no. Eg Ref Desc Value 1.00 HOL <= = = this row is the header row and will be bold 1.01 1st 10.00 1.14 2nd 25.00 SOME TEXT ON ROws which prevent me from using a Pivot table 2.00 WORK <= = = this is also a header row and will be bold 2.14 wed 105.20 2.14.1 thurs 99.6 The desired result is Ref Desc SubTotal 1.00 HOL 35.00 2.00 WORK 204.80 The subtotal needs to be for each change in whole number to the left of the decimal point Any ideas Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
subtotal based on ref no
Hi,
If you can use another column, you can value the first digit, but this would also assume that the number is only 1 digit. In column D try: =VALUE(LEFT(A2,1)), which would give you a digit to subtotal on. -- David "Newbie" wrote: Excel 2003 and XP I have a spreadsheet that can't be changed so in a separate sheet I want to be able to create totals of variable ranges. Each range changes based on the reference no. Eg Ref Desc Value 1.00 HOL <= = = this row is the header row and will be bold 1.01 1st 10.00 1.14 2nd 25.00 SOME TEXT ON ROws which prevent me from using a Pivot table 2.00 WORK <= = = this is also a header row and will be bold 2.14 wed 105.20 2.14.1 thurs 99.6 The desired result is Ref Desc SubTotal 1.00 HOL 35.00 2.00 WORK 204.80 The subtotal needs to be for each change in whole number to the left of the decimal point Any ideas Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
subtotal based on ref no
Thanks but the value can be anything
Is there a way to only ref the whole number part i.e always round down in VBA? "David" wrote in message ... Hi, If you can use another column, you can value the first digit, but this would also assume that the number is only 1 digit. In column D try: =VALUE(LEFT(A2,1)), which would give you a digit to subtotal on. -- David "Newbie" wrote: Excel 2003 and XP I have a spreadsheet that can't be changed so in a separate sheet I want to be able to create totals of variable ranges. Each range changes based on the reference no. Eg Ref Desc Value 1.00 HOL <= = = this row is the header row and will be bold 1.01 1st 10.00 1.14 2nd 25.00 SOME TEXT ON ROws which prevent me from using a Pivot table 2.00 WORK <= = = this is also a header row and will be bold 2.14 wed 105.20 2.14.1 thurs 99.6 The desired result is Ref Desc SubTotal 1.00 HOL 35.00 2.00 WORK 204.80 The subtotal needs to be for each change in whole number to the left of the decimal point Any ideas Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
subtotal based on ref no
Hi Again,
Try this formula: =ROUNDDOWN(A2,0) -- David "Newbie" wrote: Thanks but the value can be anything Is there a way to only ref the whole number part i.e always round down in VBA? "David" wrote in message ... Hi, If you can use another column, you can value the first digit, but this would also assume that the number is only 1 digit. In column D try: =VALUE(LEFT(A2,1)), which would give you a digit to subtotal on. -- David "Newbie" wrote: Excel 2003 and XP I have a spreadsheet that can't be changed so in a separate sheet I want to be able to create totals of variable ranges. Each range changes based on the reference no. Eg Ref Desc Value 1.00 HOL <= = = this row is the header row and will be bold 1.01 1st 10.00 1.14 2nd 25.00 SOME TEXT ON ROws which prevent me from using a Pivot table 2.00 WORK <= = = this is also a header row and will be bold 2.14 wed 105.20 2.14.1 thurs 99.6 The desired result is Ref Desc SubTotal 1.00 HOL 35.00 2.00 WORK 204.80 The subtotal needs to be for each change in whole number to the left of the decimal point Any ideas Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
subtotal based on ref no
thanks but rounddown is not available in vba
"David" wrote in message ... Hi Again, Try this formula: =ROUNDDOWN(A2,0) -- David "Newbie" wrote: Thanks but the value can be anything Is there a way to only ref the whole number part i.e always round down in VBA? "David" wrote in message ... Hi, If you can use another column, you can value the first digit, but this would also assume that the number is only 1 digit. In column D try: =VALUE(LEFT(A2,1)), which would give you a digit to subtotal on. -- David "Newbie" wrote: Excel 2003 and XP I have a spreadsheet that can't be changed so in a separate sheet I want to be able to create totals of variable ranges. Each range changes based on the reference no. Eg Ref Desc Value 1.00 HOL <= = = this row is the header row and will be bold 1.01 1st 10.00 1.14 2nd 25.00 SOME TEXT ON ROws which prevent me from using a Pivot table 2.00 WORK <= = = this is also a header row and will be bold 2.14 wed 105.20 2.14.1 thurs 99.6 The desired result is Ref Desc SubTotal 1.00 HOL 35.00 2.00 WORK 204.80 The subtotal needs to be for each change in whole number to the left of the decimal point Any ideas Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal - Based on DATE | Excel Worksheet Functions | |||
Subtotal based discounts | Excel Discussion (Misc queries) | |||
How to calculate shipping costs based on subtotal | Excel Worksheet Functions | |||
subtotal based on two conditions | Excel Discussion (Misc queries) | |||
I need to subtotal based on an if | Excel Worksheet Functions |