Formula
 

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 target
acheived. I need a formula in the target acheived column.

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

Cheers,
Sathish


--
Sathisc
------------------------------------------------------------------------
Sathisc's Profile: http://www.thecodecage.com/forumz/member.php?userid=187
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=77749

Formula
 

Sathisc;278308 Wrote:
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 target
acheived. I need a formula in the target acheived column.

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

Cheers,
Sathish
Welcome to The Code Cage!

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


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=77749

Formula
 

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

"Sathisc" wrote:


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 target
acheived. I need a formula in the target acheived column.

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

Cheers,
Sathish


--
Sathisc
------------------------------------------------------------------------
Sathisc's Profile: http://www.thecodecage.com/forumz/member.php?userid=187
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=77749

Formula
 

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.


--
Sathisc
------------------------------------------------------------------------
Sathisc's Profile: http://www.thecodecage.com/forumz/member.php?userid=187
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=77749

Formula
 

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


"Sathisc" wrote:


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.


--
Sathisc
------------------------------------------------------------------------
Sathisc's Profile: http://www.thecodecage.com/forumz/member.php?userid=187
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=77749

Formula
 

I tried again but it is not working withe the if statement. 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.

I have attached the excel

help needed


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

--
Sathisc
------------------------------------------------------------------------
Sathisc's Profile: http://www.thecodecage.com/forumz/member.php?userid=187
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=77749

Formula
 

On Mar 21, 6:32 am, Sathisc wrote:
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.

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

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 -----

On Mar 21, 6:32*am, Sathisc wrote:
I tried again but it is not working withe the if statement.
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.

I have attached the excel

help needed

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

--
Sathisc
------------------------------------------------------------------------

Formula
 

Thank you so much mate.

cheers


--
Sathisc
------------------------------------------------------------------------
Sathisc's Profile: http://www.thecodecage.com/forumz/member.php?userid=187
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=77749