OpenOffice/Excel formula

  Boluwd 15:52 01 Apr 04
Locked

I am creating a simple monthly hours worked/payment spreadsheet for the wife who teaches aerobics. One column is headed "duration" (Col C) and the next column is "rate per hour" (Col D) and obviously the next column is "payment" (Col E). There are other columns (Date, time etc,) but for the purpose of the query the cells in E = C*D.

In order to avoid typing in the rate per hour every time in each new row, I have one cell say B4 which is the rate per hour and all the cells in column D =$B$4. Easy peasey!! What happens if there is a pay rise (or cut) where the value of B4 changes. How can I put in a formula in Column D which reflects the new rate but does not change all the entries already referencing the old rate. (ie cells D1 to D9 were correctly showing 10 but from D10 onwards the new rate needs to show 12)

I'm actually using Open Office for this but I thought if I mention Excel in the header it would arouse VoG! It's probably less hassle to just enter the rate per hour in each cell of Column D each time but if any of you have some bright ideas I'd be grateful.

  VoG II 15:59 01 Apr 04

I don't know whether this will work in Open Office.

Select all the cells in Column D with the old rate and Copy. Then Edit/Paste Special and tick Values. This will convert all the =$B$4 to the actual values. Then you can change the rate without changing the already entered values.

  xania 16:03 01 Apr 04

In Excel there is an easier option. Insert the rate into any cell outside the main spreadsheet. You can then name the cell <Insert><Name>. Now, instead of a constnat, use this name in your formulae. Any change in the value in the constant cell will now automatically update every cell that references it.

  VoG II 16:05 01 Apr 04

That's what he wants to avoid!

  Boluwd 16:12 01 Apr 04

Thanks VoG™, this works if I substitue "numbers" for "value" in Open Office's Paste Special menu. You are a genius Sir!

  Sir Radfordin 16:14 01 Apr 04

VOGs method should work but another approach would be to create a parameters sheet that would allow you to store several hourly rates and then you would just need to change the formular in the cells that you haven't already calcualated values for.

  Boluwd 16:20 01 Apr 04

Thanks everyone for your contributions. I'm going with VoG™ on this one as it suits the layout of the whole "workbook"...... and it's wifeproof!

Cheers.

  Sir Radfordin 16:22 01 Apr 04

Don't be silly nothing is wifeproof ;)

  Boluwd 16:26 01 Apr 04

lol :))

This thread is now locked and can not be replied to.

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

An overview: What leading creative agencies are doing to improve diversity

New iPad, iPhone SE & Red iPhone 7 on sale now