Urgent - how to set a formula in excel spreadsheet

[Guest]

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 time column C
A 1 0-90 Days
B Not delivered yet Not delivered yet
C 282 181-365 Days
D 86 0-90 Days
E 109 91-180 Days
A 304 181-365 Days
B 209 181-365 Days
C 144 91-180 Days
D 67 0-90 Days
E 352 181-365 Days
F 35 0-90 Days
G 180 91-180 Days
H Not delivered yet Not delivered yet


Please 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

 

[rocket0612]

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

 

[Dav]

If you did not have not delivered you could use a lookup table, but the
following should suffice

=IF(B1<91,"0-90 days",IF(B1<181,"91-180 days",IF(B1<366,"181-365
days","Not Delivered")))

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(B1<91,ISNUMBER(B1)),"0-90
days",IF(AND(B1<181,ISNUMBER(B1)),"91-180
days",IF(AND(B1<366,ISNUMBER(B1)),"181-365 days","Not Delivered")))

the above can probably be shorter but it works!

Regards

Dav

 

[Guest]

Dear xxx,

If the titles are in first row i.e., "Product tour aorund time column C",
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="Not delivered yet","Not delivered yet",IF(B2<=90,"0-90
Days",IF(B2<=180,"91-180 Days",IF(B2<=365,"181-365 Days","Invalid Input"))))


Thanks & regards,

Bye
NAVEEN

 

[Guest]

Perhaps something like this:

For text or number of days in B2
C2: =IF(OR(ISTEXT(B2),ISBLANK(B2)),B2&"",(TRUNC((B2-1)/90)*90+1)&" to
"&TRUNC((B2+89)/90)*90&" days")

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