software

Hi all,

Please help me on the following:

I have about 1328 row of records with mixture of different products, those
products delivered with different turn around time.

For example

Product tour aorund timecolumn C
A1 0-90 Days
BNot delivered yetNot delivered yet
C282 181-365 Days
D86 0-90 Days
E109 91-180 Days
A304 181-365 Days
B209 181-365 Days
C144 91-180 Days
D67 0-90 Days
E352 181-365 Days
F35 0-90 Days
G180 91-180 Days
HNot delivered yet Not delivered yetPlease help me on how to set a formula in column C for 4 categories stated
above, as it is very time consuming to manual put in the category for 1328
rows.

Thanks
You haven't stated clearly to me what you are trying to achieve, is it
you want to be able to input 'A' and it will automatically bring up
'0-90 days'?--
rocket0612
------------------------------------------------------------------------
rocket0612's Profile: www.excelforum.com/member.php...oamp;userid=19492
View this thread: www.excelforum.com/showthread...hreadid=514321
If you did not have not delivered you could use a lookup table, but the
following should suffice

=IF(B1lt;91,quot;0-90 daysquot;,IF(B1lt;181,quot;91-180 daysquot;,IF(B1lt;366,quot;181-365
daysquot;,quot;Not Deliveredquot;)))

but a blank will return 0-90 days and anything above 365 eg 400 will
return nor delivered

If you wish to exclude blank

=IF(AND(B1lt;91,ISNUMBER(B1)),quot;0-90
daysquot;,IF(AND(B1lt;181,ISNUMBER(B1)),quot;91-180
daysquot;,IF(AND(B1lt;366,ISNUMBER(B1)),quot;181-365 daysquot;,quot;Not Deliveredquot;)))

the above can probably be shorter but it works!

Regards

Dav--
Dav
------------------------------------------------------------------------
Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
View this thread: www.excelforum.com/showthread...hreadid=514321Dear xxx,

If the titles are in first row i.e., quot;Product tour aorund timecolumn Cquot;,
start from the second row and type the following formula in C2 and copy and
paste it to the following cells to get the required results.

=IF(B2=quot;Not delivered yetquot;,quot;Not delivered yetquot;,IF(B2lt;=90,quot;0-90
Daysquot;,IF(B2lt;=180,quot;91-180 Daysquot;,IF(B2lt;=365,quot;181-365 Daysquot;,quot;Invalid Inputquot;))))Thanks amp; regards,

Bye
NAVEEN

quot;Calculate Date rangequot; wrote:

gt; Hi all,
gt;
gt; Please help me on the following:
gt;
gt; I have about 1328 row of records with mixture of different products, those
gt; products delivered with different turn around time.
gt;
gt; For example
gt;
gt; Product tour aorund timecolumn C
gt; A1 0-90 Days
gt; BNot delivered yetNot delivered yet
gt; C282 181-365 Days
gt; D86 0-90 Days
gt; E109 91-180 Days
gt; A304 181-365 Days
gt; B209 181-365 Days
gt; C144 91-180 Days
gt; D67 0-90 Days
gt; E352 181-365 Days
gt; F35 0-90 Days
gt; G180 91-180 Days
gt; HNot delivered yet Not delivered yet
gt;
gt;
gt; Please help me on how to set a formula in column C for 4 categories stated
gt; above, as it is very time consuming to manual put in the category for 1328
gt; rows.
gt;
gt; Thanks
gt;

Perhaps something like this:

For text or number of days in B2
C2: =IF(OR(ISTEXT(B2),ISBLANK(B2)),B2amp;quot;quot;,(TRUNC((B2-1)/90)*90 1)amp;quot; to
quot;amp;TRUNC((B2 89)/90)*90amp;quot; daysquot;)

Blanks or text return the value of B2
Examples:
B2: Still verifying
C2: Still verifying

B2: 407
C2: 361 to 450 days

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Proquot;Calculate Date rangequot; wrote:

gt; Hi all,
gt;
gt; Please help me on the following:
gt;
gt; I have about 1328 row of records with mixture of different products, those
gt; products delivered with different turn around time.
gt;
gt; For example
gt;
gt; Product tour aorund timecolumn C
gt; A1 0-90 Days
gt; BNot delivered yetNot delivered yet
gt; C282 181-365 Days
gt; D86 0-90 Days
gt; E109 91-180 Days
gt; A304 181-365 Days
gt; B209 181-365 Days
gt; C144 91-180 Days
gt; D67 0-90 Days
gt; E352 181-365 Days
gt; F35 0-90 Days
gt; G180 91-180 Days
gt; HNot delivered yet Not delivered yet
gt;
gt;
gt; Please help me on how to set a formula in column C for 4 categories stated
gt; above, as it is very time consuming to manual put in the category for 1328
gt; rows.
gt;
gt; Thanks
gt;