Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I use Dynamic Ranges to automate complex calculations?
I have read lots of threads on how to create Dynamic Ranges. I also know I
can use it to automatically update a chart or do some simple calculations. But I am still struggling to do the following: Suppose I have Column A as a dynamic range called "Date", Column B called "Rate" in the tab "Input". I can type in date and interest rate each day manually and I know the ranges will automatically expand. However, I want to do calculations in another Tab "Cal", such as dividing the interests by 12 to get monthly rate in column A, then Imultipling it with a constant say $1,000. My question is, is it possible to have Excel automatically add rows in tab "Cal" and do the calculations whenever a new row in Tab "Input" is added? "Input" Tab A B (Date) (Rate) 1 1/1 5.5% 2. 1/2 6.0% 3. 1/3 6.2% "Cal" Tab A B MthlyRate Times 1,000 1 0.458% $4.58 2 0.500% $5.00 3 0.517% $5.17 (Can calculations in Cal Tab be populated once the dynamic ranges in Input Tab expands?) THANKS! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I use Dynamic Ranges to automate complex calculations?
Hi Exceluser (aren't we all :D )
If you don't use VBA you cannot create formulas in cells automatically. what you can do though, without VBA is following: you can put a if statement in your formulas in Worksheet Cal (call it worksheet not Tab, it helps to understand each other better) like: Worksheet Cal Cell A2 =if(input!A2="","",YOURFORMULA) put your formula in it and then copy it down let's say the first 100 rows. That way you don't see anything, but as soon as you enter something the formula will return you your calculated result. hth Carlo On Dec 21, 1:57*pm, Exceluser wrote: I have read lots of threads on how to create Dynamic Ranges. I also know I can use it to automatically update a chart or do some simple calculations. But I am still struggling to do the following: Suppose I have Column A as a dynamic range called "Date", Column B called "Rate" in the tab "Input". I can type in date and interest rate each day manually and I know the ranges will automatically expand. However, I want to do calculations in another Tab "Cal", such as dividing the interests by 12 to get monthly rate in column A, then Imultipling it with a constant say $1,000. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I use Dynamic Ranges to automate complex calculations?
One alternative to achieve the intended visual/appearance is to mask it using
an IF, with formulas then copied down to cover the max expected extent of source data (in advance of expected source data) For example, in your sheet: Calc, You could put in A1: =IF(Input!B1="","",Input!B1/12) in B1: =IF(A1="","",A1*1000) format A1 as percentage, B1 as currency, then select A1:B1 and copy down to cover the max expected extent of data in Input's col B. As Input's col B gets progressively filled down, Calc's cols A and B will also reflect progressively. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Exceluser" wrote: I have read lots of threads on how to create Dynamic Ranges. I also know I can use it to automatically update a chart or do some simple calculations. But I am still struggling to do the following: Suppose I have Column A as a dynamic range called "Date", Column B called "Rate" in the tab "Input". I can type in date and interest rate each day manually and I know the ranges will automatically expand. However, I want to do calculations in another Tab "Cal", such as dividing the interests by 12 to get monthly rate in column A, then Imultipling it with a constant say $1,000. My question is, is it possible to have Excel automatically add rows in tab "Cal" and do the calculations whenever a new row in Tab "Input" is added? "Input" Tab A B (Date) (Rate) 1 1/1 5.5% 2. 1/2 6.0% 3. 1/3 6.2% "Cal" Tab A B MthlyRate Times 1,000 1 0.458% $4.58 2 0.500% $5.00 3 0.517% $5.17 (Can calculations in Cal Tab be populated once the dynamic ranges in Input Tab expands?) THANKS! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I use Dynamic Ranges to automate complex calculations?
Max: thank you. Happy holidays! Tao
"Max" wrote: One alternative to achieve the intended visual/appearance is to mask it using an IF, with formulas then copied down to cover the max expected extent of source data (in advance of expected source data) For example, in your sheet: Calc, You could put in A1: =IF(Input!B1="","",Input!B1/12) in B1: =IF(A1="","",A1*1000) format A1 as percentage, B1 as currency, then select A1:B1 and copy down to cover the max expected extent of data in Input's col B. As Input's col B gets progressively filled down, Calc's cols A and B will also reflect progressively. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Exceluser" wrote: I have read lots of threads on how to create Dynamic Ranges. I also know I can use it to automatically update a chart or do some simple calculations. But I am still struggling to do the following: Suppose I have Column A as a dynamic range called "Date", Column B called "Rate" in the tab "Input". I can type in date and interest rate each day manually and I know the ranges will automatically expand. However, I want to do calculations in another Tab "Cal", such as dividing the interests by 12 to get monthly rate in column A, then Imultipling it with a constant say $1,000. My question is, is it possible to have Excel automatically add rows in tab "Cal" and do the calculations whenever a new row in Tab "Input" is added? "Input" Tab A B (Date) (Rate) 1 1/1 5.5% 2. 1/2 6.0% 3. 1/3 6.2% "Cal" Tab A B MthlyRate Times 1,000 1 0.458% $4.58 2 0.500% $5.00 3 0.517% $5.17 (Can calculations in Cal Tab be populated once the dynamic ranges in Input Tab expands?) THANKS! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I use Dynamic Ranges to automate complex calculations?
Thank you, Carlo. That's what I thought about, too. I guess VBA has to be
used, at which I am not good. How hard would the VBA codes be in this case if I want to use them? Happy Holidays! Tao "carlo" wrote: Hi Exceluser (aren't we all :D ) If you don't use VBA you cannot create formulas in cells automatically. what you can do though, without VBA is following: you can put a if statement in your formulas in Worksheet Cal (call it worksheet not Tab, it helps to understand each other better) like: Worksheet Cal Cell A2 =if(input!A2="","",YOURFORMULA) put your formula in it and then copy it down let's say the first 100 rows. That way you don't see anything, but as soon as you enter something the formula will return you your calculated result. hth Carlo On Dec 21, 1:57 pm, Exceluser wrote: I have read lots of threads on how to create Dynamic Ranges. I also know I can use it to automatically update a chart or do some simple calculations. But I am still struggling to do the following: Suppose I have Column A as a dynamic range called "Date", Column B called "Rate" in the tab "Input". I can type in date and interest rate each day manually and I know the ranges will automatically expand. However, I want to do calculations in another Tab "Cal", such as dividing the interests by 12 to get monthly rate in column A, then Imultipling it with a constant say $1,000. My question is, is it possible to have Excel automatically add rows in tab "Cal" and do the calculations whenever a new row in Tab "Input" is added? "Input" Tab A B (Date) (Rate) 1 1/1 5.5% 2. 1/2 6.0% 3. 1/3 6.2% "Cal" Tab A B MthlyRate Times 1,000 1 0.458% $4.58 2 0.500% $5.00 3 0.517% $5.17 (Can calculations in Cal Tab be populated once the dynamic ranges in Input Tab expands?) THANKS! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I use Dynamic Ranges to automate complex calculations?
welcome, same to you.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Exceluser" wrote in message ... Max: thank you. Happy holidays! Tao |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Iterations of complex calculations | Excel Discussion (Misc queries) | |||
Simple, yet complex problem! Using results as new data during calculations? | Excel Worksheet Functions | |||
complex calculations | Excel Discussion (Misc queries) | |||
Automate a complex decision structure | Excel Worksheet Functions | |||
automate printing different ranges on 1 worksheet at different tim | Excel Discussion (Misc queries) |