[Excel 2007] How do I program this simple function?

I'm using a function to put a price in a cell depending on how many days have passed. So 1 day = $10, 2 days = $20, 3 days = $30 and I do it like this...

A1 (start date) = 01/01/2011
B1 (end date) = 01/01/2011
C1 (days) = 1
D1 (price) = $10

But after 30 days I want to put in a discounted price (so 31 days would be $305 not $310) and this is where I get stuck. My code goes something like this (I'm not in front of the same computer right now)...

Code:

---

elseif days > 30 then
x = range("c31") - 30
price = 300 + (x * 5)

---

Initially the code worked but then after copying and pasting the code into other cells/rows below it, I was getting the wrong result. I think it's because I'm referencing the cell directly "C31". I think I need to program it so that when I copy and paste the cells contents the reference to the cell changes too (eg - C31 to C32) but I can't work out how to fix this problem. So can anyone point me in the right direction? Thanks.

I would say:

Code:

---

elseif days>30 then
  price=300+((days-30)*5)

---

Thanks opus. I'll give it a shot when I can and I'll let you know how I go.

As a possibly silly question - why in code? Can't you do one or two columns with the results and use a function to give you the results?


Complicated formulae but you needn't use code (unless you are are already and its to put it in place...)


Hope those who have posted already have given you the answer you need.