If statement formula
 

Column A contains the date and Column B contains the data. I would like to
create an IF statement showing the date of any data in Column B that exceeds
100.

Hi
use 'Data - Filter - autofilter' for this

--
Regards
Frank Kabel
Frankfurt, Germany

"lintan" schrieb im Newsbeitrag
...
Column A contains the date and Column B contains the data. I would
like to
create an IF statement showing the date of any data in Column B that
exceeds
100.

If A does actually contain a date and you simply want to make it visible/not
based on B, you can use conditional formatting.

Choose the A cell like A1

formatconditional formattingformula is =B1<=100
and set the format the same color as the background (making them
indistinguishable).

You can then copy this A1 formula down through A2:A2000 or whatever using
copy/paste specialformats.

"lintan" wrote in message
...
Column A contains the date and Column B contains the data. I would like
to
create an IF statement showing the date of any data in Column B that
exceeds
100.

Thank for your help. What I actually want to do is if any data in Column B
exceeds 100, a statement such as "Usage exceeded limit on (the date of
exceedance)" will show up. Below is part of my spreadsheet:

Column A Column B
1/1/04 90
1/2/04 103
1/3/04 110
1/4/04 85

I want to show a statement saying "Usage exceeded limit on 1/2/04, 1/3/04".


"Dave R." wrote:

If A does actually contain a date and you simply want to make it visible/not
based on B, you can use conditional formatting.

Choose the A cell like A1

formatconditional formattingformula is =B1<=100
and set the format the same color as the background (making them
indistinguishable).

You can then copy this A1 formula down through A2:A2000 or whatever using
copy/paste specialformats.

"lintan" wrote in message
...
Column A contains the date and Column B contains the data. I would like
to
create an IF statement showing the date of any data in Column B that
exceeds
100.

In C1
=IF(B1100,TEXT(A1,"m/d/yy")&" ","")
then paste through C5 then use

="Usage exceeded: "&C1&C2&C3&C4&C5



"lintan" wrote in message
...
Thank for your help. What I actually want to do is if any data in Column
B
exceeds 100, a statement such as "Usage exceeded limit on (the date of
exceedance)" will show up. Below is part of my spreadsheet:

Column A Column B
1/1/04 90
1/2/04 103
1/3/04 110
1/4/04 85

I want to show a statement saying "Usage exceeded limit on 1/2/04,
1/3/04".


"Dave R." wrote:

If A does actually contain a date and you simply want to make it
visible/not
based on B, you can use conditional formatting.

Choose the A cell like A1

formatconditional formattingformula is =B1<=100
and set the format the same color as the background (making them
indistinguishable).

You can then copy this A1 formula down through A2:A2000 or whatever
using
copy/paste specialformats.

"lintan" wrote in message
...
Column A contains the date and Column B contains the data. I would
like
to
create an IF statement showing the date of any data in Column B that
exceeds
100.

lintan Wrote:
Column A contains the date and Column B contains the data. I would like
to
create an IF statement showing the date of any data in Column B that
exceeds
100.

Assume that the date is in Column A and the data in Column B, enter
this formula in Cell C1 and copy down as required:

=if(B1100,A1,"")


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=319402

This works but how do I separate the dates in the "Usage exceeded:
"&C1&C2&C3&C4&C5"

"Dave R." wrote:

In C1
=IF(B1100,TEXT(A1,"m/d/yy")&" ","")
then paste through C5 then use

="Usage exceeded: "&C1&C2&C3&C4&C5



"lintan" wrote in message
...
Thank for your help. What I actually want to do is if any data in Column
B
exceeds 100, a statement such as "Usage exceeded limit on (the date of
exceedance)" will show up. Below is part of my spreadsheet:

Column A Column B
1/1/04 90
1/2/04 103
1/3/04 110
1/4/04 85

I want to show a statement saying "Usage exceeded limit on 1/2/04,
1/3/04".


"Dave R." wrote:

If A does actually contain a date and you simply want to make it
visible/not
based on B, you can use conditional formatting.

Choose the A cell like A1

formatconditional formattingformula is =B1<=100
and set the format the same color as the background (making them
indistinguishable).

You can then copy this A1 formula down through A2:A2000 or whatever
using
copy/paste specialformats.

"lintan" wrote in message
...
Column A contains the date and Column B contains the data. I would
like
to
create an IF statement showing the date of any data in Column B that
exceeds
100.