Technical Discussion
  >> Windows Issues


Register (or login) on our website and you will not see this ad.


  Print Thread
Anonymous
(Unregistered)Sat 01-Sep-12 14:12:38
Print Post

Excel Question


[link to this post]
 
Some help with an Excel formula please.

The formula is for working out time costs

Cell A1 will be marked with an X or left blank

So when time is place in cell C1 the hourly rate will be calculated at one of two rates, with the X in cell A1 deciding which hourly rate to apply.

So if I place the formula =IF(A1>0, (C1*24)*100) in D1, mark A1 with a X and enter 10:00 in C1 I get the correct answer of £1000 in D1.

What I want to do is expand the formula (or find a better way) so that in D1 if A1 has no X, it will calculate the costs at £50 per hour and if it has an X, calculate the costs at £100.

Thanks
Standard User BatBoy
(legend) Sat 01-Sep-12 14:24:50
Print Post

Re: Excel Question


[re: Anonymous] [link to this post]
 
Put the hourly rate in A1


_____________________________________________________________________________________________ this is not usenet __________________
Anonymous
(Unregistered)Sat 01-Sep-12 15:11:10
Print Post

Re: Excel Question


[re: BatBoy] [link to this post]
 
Thanks or the reply, I've probably over simplified what I aim to achieve.

The worksheet will contain a series of times for different types of work for which two different hourly rates will apply. So C1 to C12 will contain different times for which two different hourly rates will apply so for example the two hourly rates for c1 are £100 and £50 for C2 times they are £30 and £15 and for C3 £45 and £25 etc. So the hourly rates need to be in the formula with the only items being entered on the sheet are the X (or not) and the time.


Register (or login) on our website and you will not see this ad.

Standard User BatBoy
(legend) Sat 01-Sep-12 15:15:53
Print Post

Re: Excel Question


[re: Anonymous] [link to this post]
 
=IF(A1="X", C1*24*100,C1*24*50)


_____________________________________________________________________________________________ this is not usenet __________________
Anonymous
(Unregistered)Sat 01-Sep-12 16:18:13
Print Post

Re: Excel Question


[re: BatBoy] [link to this post]
 
Thanks, worked a treat.
Standard User ian72
(knowledge is power) Mon 03-Sep-12 09:23:03
Print Post

Re: Excel Question


[re: Anonymous] [link to this post]
 
Obviously Batboy's formula works perfectly but the other way would be:

=C1*24*IF(A1="X", 100,50)

Slighly more "efficient" and also it is a little clearer. Plus if you need to change the 24 multiplier or other parts of the calc you only need to change them once rather than for both calculations.

If you ended up needing more rates it could also be easily switched to use a lookup table just by replacing the if with a vlookup.
Standard User philippercival
(experienced) Mon 03-Sep-12 11:10:36
Print Post

Re: Excel Question


[re: ian72] [link to this post]
 
Neat smile

  Print Thread

Jump to