calculating course fees

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello from Ottawa Canada.

Simply put, i've never used excel before. I'm trying to simplify a web page
to calculate the price changes

http://www.cpc.gc.ca/courses/fee_c45_e.htm

The prices for the meals and lodging often change and i have to make the
changes manually and as you can see it's a big page. I have four like that.

I want to have a template that let's me set the price for the meals and
lodging and to have the changes recalculated depending on how many days
accomodation the course is plus tuition.

Hope this is understandable

thanks
 
Put the raw data and calculations in one worksheet. Put the formatted
results in another worksheet. Export the 2nd worksheet as a web page.

Suppose the raw data are laid out as:
A1:Courses
B1:Course Abbreviation
C1:Number of Days Training
D1:Tuition
E1:Number of Days Accommodation
F1:Lodgings
G1:Meals
H1:Total Cost
F2:65
G2: 30.3
Then, the following are sample records (note the alignment after
posting may be out-of-whack) starting with row 3:
Advanced Forensic Digital Imaging ADIGIM 5 250 5 325
151.5 726.5
Advanced Friction Ridge Analysis ARIDGE 5 250 5 325
151.5 726.5
Advanced Vehicle Theft Investigative Techniques AVTITC 10 500
12 780 363.6 1643.6
Criminal Intelligence Analysis -Distance Learning DCIAC *DL 200
DL 0 0 200
The last 3 cells in each row (F3, G3, and H3 for example) are
calculated as
=IF(ISNUMBER($E3),F$2*$E3,0)
=IF(ISNUMBER($E3),G$2*$E3,0) and
=D3+F3+G3

Suppose the above sheet is named Sheet1. Then, on another sheet, put
the headings as appropriate. Make sure the cells in column A have the
proper hyperlinks. Then, enter the formulas
C3:="Days Training " &Sheet1!C3
D3:="Tuition "&TEXT(Sheet1!D3,"$#,##0.00")
E3: ="Days Accomm. " &Sheet1!E3
F3:="Lodgings "&TEXT(Sheet1!F3,"$#,##0.00")
G3:="Meals "&TEXT(Sheet1!G3,"$#,##0.00")
H3:="Total Cost "&TEXT(Sheet1!H3,"$#,##0.00")

Copy C3:H3 as far down the rows as needed.
Export the contents of this 2nd sheet to a web page.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top