Formula

S

Sathisc

Hi,

I need a help in creating a formula :


COMPLETION TIME TARGET ACHIEVED
10:23
12:20
13:30
14:01
15:00

two seperate colums one for completion time and another for targe
acheived. I need a formula in the target acheived column.

If the time completion is before 14:00 the target achieved should b
100%. if it corsses 14:00 then the target acheived should be 0%.

Cheers,
Sathis
 
S

Simon Lloyd

Sathisc;278308 said:
Hi

I need a help in creating a formula


COMPLETION TIME TARGET ACHIEVE
10:23
12:20
13:30
14:01
15:00

two seperate colums one for completion time and another for targe
acheived. I need a formula in the target acheived column

If the time completion is before 14:00 the target achieved should b
100%. if it corsses 14:00 then the target acheived should be 0%

Cheers
Sathis
Click to expand...
Welcome to The Code Cage

Are your cells formatted as time or have you entered times as text

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com
 
M

Mike H

Hi,

I'm assuming your first time 10:23 is in a2. Put this in B2, format as
percentage and drag down

=IF(A2<TIME(14,0,0),100%,0%)

or if 14:00 = 100%

=IF(A2<=TIME(14,0,0),100%,0%)

Mike
 
S

Sathisc

Thanks guys its working perfect,

When i average the 100% for all the days the average is not working.

A B

10:40 100
11:12 100
11:02 100
10:00 100
11:14 100

10:49 #DIV/0!

In A7 i have averaged the time and in B7 i couldnt able to average all
the b column.

can you help in this.
 
M

Mike H

Hi,

these 2 work fine for me

Column A - average of the times
=AVERAGE(A2:A5)

Column B average of the percentages

=AVERAGE(B2:B5)

A good tip is always pot your formula even if you know it's wrong.

Mike
 
S

Sathisc

I tried again but it is not working withe the if statement. If i giv
100 in the target field manually it works but when the 100 is generate
via if formula. i couldnt able to get the average.

I have attached the excel

help neede

+-------------------------------------------------------------------
|Filename: FORMULA.xls
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=100
+-------------------------------------------------------------------
 
J

joeu2004

If i give 100 in the target field manually it works but
when the 100 is generated via if formula. i couldnt able
to get the average.
Click to expand...

Because you put quotes around the numbers. You entered:

=IF(A2<TIME(14,0,0),"100","0%")

The correct formula, as MikeH wrote it, is:

=IF(A2<TIME(14,0,0),100%,0%)

Notice __two__ corrections: (a) "0%" changed to 0% (no quotes); and
(b) "100" changed to 100% (no quotes; % added).

I have attached the excel
Click to expand...

For the future, it would have so much simpler (for us, if not you) if
you had simply cut-and-pasted the formulas into your posting. This
spreadsheet was not so complex that it required posting in its
entirety.


----- original posting -----
 
Top