What functions should I use?...please assist.

[edk256]

I have a problem with Excel. I have a worksheet with two columns. In one column is a date when an event was due, and in the other, when it was completed. What I want to do is to easily figure out how many items were completed after they were due.

What I would like to do is have Excel recognize that one date (the completed date) is after the due date, and if that were true, to generate the number 1 in a third column, in the same row for that event. After this I would simply sum the 1s in this third column to get the result.

What would the function or series of functions be to recognize that if one value in one column is larger the other value in the other column, to insert a value (1) into a third column?

I would want to apply this to several hundred rows, and hence, events.

Thank you in advance.

[Mr. Smith]

edk256
Given that your due date is in columm A and the complete date in column B
this forumla will return 1 if complet date is "bigger" than due date.
=IF(B3>A3;1;"")
The formula will leave the cell blank if due date is "bigger" or the same as
complete date.

Mr. Smith

"edk256" <edk256.2aubzr_1152721506.2829@excelforum-nospam.com> wrote in
message news:edk256.2aubzr_1152721506.2829@excelforum-nospam.com...
>
> I have a problem with Excel. I have a worksheet with two columns. In
> one column is a date when an event was due, and in the other, when it
> was completed. What I want to do is to easily figure out how many
> items were completed after they were due.
>
> What I would like to do is have Excel recognize that one date (the
> completed date) is after the due date, and if that were true, to
> generate the number 1 in a third column, in the same row for that
> event. After this I would simply sum the 1s in this third column to
> get the result.
>
> What would the function or series of functions be to recognize that if
> one value in one column is larger the other value in the other column,
> to insert a value (1) into a third column?
>
> I would want to apply this to several hundred rows, and hence, events.
>
> Thank you in advance.
>
>
> --
> edk256
> ------------------------------------------------------------------------
> edk256's Profile:
> http://www.excelforum.com/member.php...o&userid=36299
> View this thread: http://www.excelforum.com/showthread...hreadid=560756
>

[Ardus Petus]

You could simply enter following formula that will calculate the total
number of rows where completed > due:

=SUMPRODUCT(--(A1:A100>B1:B100))

If you DO want to apply your method, enter:
=--(A1>B1)
and drag down

HTH
--
AP

"edk256" <edk256.2aubzr_1152721506.2829@excelforum-nospam.com> a écrit dans
le message de news: edk256.2aubzr_1152721506.2829@excelforum-nospam.com...
>
> I have a problem with Excel. I have a worksheet with two columns. In
> one column is a date when an event was due, and in the other, when it
> was completed. What I want to do is to easily figure out how many
> items were completed after they were due.
>
> What I would like to do is have Excel recognize that one date (the
> completed date) is after the due date, and if that were true, to
> generate the number 1 in a third column, in the same row for that
> event. After this I would simply sum the 1s in this third column to
> get the result.
>
> What would the function or series of functions be to recognize that if
> one value in one column is larger the other value in the other column,
> to insert a value (1) into a third column?
>
> I would want to apply this to several hundred rows, and hence, events.
>
> Thank you in advance.
>
>
> --
> edk256
> ------------------------------------------------------------------------
> edk256's Profile:
> http://www.excelforum.com/member.php...o&userid=36299
> View this thread: http://www.excelforum.com/showthread...hreadid=560756
>

[Sandy Mann]

As Bob Philips said to me recently:

>why not the more efficient

=COUNTIF(A1:A100,">"&C1)

--
HTH

Sandy
In Perth, the ancient capital of Scotland

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk


"Ardus Petus" <ardus.petus@laposte.net> wrote in message
news:ejcDyIdpGHA.1140@TK2MSFTNGP05.phx.gbl...
> You could simply enter following formula that will calculate the total
> number of rows where completed > due:
>
> =SUMPRODUCT(--(A1:A100>B1:B100))
>
> If you DO want to apply your method, enter:
> =--(A1>B1)
> and drag down
>
> HTH
> --
> AP
>
> "edk256" <edk256.2aubzr_1152721506.2829@excelforum-nospam.com> a écrit
> dans le message de news:
> edk256.2aubzr_1152721506.2829@excelforum-nospam.com...
>>
>> I have a problem with Excel. I have a worksheet with two columns. In
>> one column is a date when an event was due, and in the other, when it
>> was completed. What I want to do is to easily figure out how many
>> items were completed after they were due.
>>
>> What I would like to do is have Excel recognize that one date (the
>> completed date) is after the due date, and if that were true, to
>> generate the number 1 in a third column, in the same row for that
>> event. After this I would simply sum the 1s in this third column to
>> get the result.
>>
>> What would the function or series of functions be to recognize that if
>> one value in one column is larger the other value in the other column,
>> to insert a value (1) into a third column?
>>
>> I would want to apply this to several hundred rows, and hence, events.
>>
>> Thank you in advance.
>>
>>
>> --
>> edk256
>> ------------------------------------------------------------------------
>> edk256's Profile:
>> http://www.excelforum.com/member.php...o&userid=36299
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=560756
>>
>
>

[Sandy Mann]

Ooops! Misread what the OP was asking.

Please ignore my gaff and forgive me.

--
HTH

Sandy
In Perth, the ancient capital of Scotland

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk


"Sandy Mann" <sandymann2@mailinator.com> wrote in message
news:OostxQepGHA.3936@TK2MSFTNGP04.phx.gbl...
> As Bob Philips said to me recently:
>
>>why not the more efficient
>
> =COUNTIF(A1:A100,">"&C1)
>
> --
> HTH
>
> Sandy
> In Perth, the ancient capital of Scotland
>
> sandymann2@mailinator.com
> Replace@mailinator.com with @tiscali.co.uk
>
>
> "Ardus Petus" <ardus.petus@laposte.net> wrote in message
> news:ejcDyIdpGHA.1140@TK2MSFTNGP05.phx.gbl...
>> You could simply enter following formula that will calculate the total
>> number of rows where completed > due:
>>
>> =SUMPRODUCT(--(A1:A100>B1:B100))
>>
>> If you DO want to apply your method, enter:
>> =--(A1>B1)
>> and drag down
>>
>> HTH
>> --
>> AP
>>
>> "edk256" <edk256.2aubzr_1152721506.2829@excelforum-nospam.com> a écrit
>> dans le message de news:
>> edk256.2aubzr_1152721506.2829@excelforum-nospam.com...
>>>
>>> I have a problem with Excel. I have a worksheet with two columns. In
>>> one column is a date when an event was due, and in the other, when it
>>> was completed. What I want to do is to easily figure out how many
>>> items were completed after they were due.
>>>
>>> What I would like to do is have Excel recognize that one date (the
>>> completed date) is after the due date, and if that were true, to
>>> generate the number 1 in a third column, in the same row for that
>>> event. After this I would simply sum the 1s in this third column to
>>> get the result.
>>>
>>> What would the function or series of functions be to recognize that if
>>> one value in one column is larger the other value in the other column,
>>> to insert a value (1) into a third column?
>>>
>>> I would want to apply this to several hundred rows, and hence, events.
>>>
>>> Thank you in advance.
>>>
>>>
>>> --
>>> edk256
>>> ------------------------------------------------------------------------
>>> edk256's Profile:
>>> http://www.excelforum.com/member.php...o&userid=36299
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=560756
>>>
>>
>>
>
>

[edk256]

Someone on another Excel board suggested the following function:

Please Login or Register  to view this content.

It worked perfectly after changing the cell range and such. Thank you for your timely responses nonetheless.