Can I use Dynamic Ranges to automate complex calculations?

  • Thread starter Thread starter Exceluser
  • Start date Start date
E

Exceluser

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!
 
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
 
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: thank you. Happy holidays! Tao

Max said:
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.
Click to expand...
 
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
 
Back
Top