>= formula not calc as expected

  • Thread starter Thread starter Doug
  • Start date Start date
D

Doug

In a column with numerals which represent # of days that elapse fro
invoice date to payment date, I want to create another series of colum
that drop that info into certain "A/R aged" buckets, ie... custome
paid in 13 days, I want that to show in a column titled 0-15 days, the
if a customer pays in 17 days, it should show in a column titled 16-3
days. My formula reads...
if "cell x" is <= 15,"x","":confused
 
Hi Doug,

the "if" function is not applicable for more than 6 conditions I think
so you have to avoid it in your case. I can suggest you this:

Let's say you have the following criteria for the payments:

0 -15
16 - 30
31 - 45
46 - 60.

Suppose you have the lowest borders in column A (i.e. A1 = 0, A2 = 16
A3 = 31, A4 = 46) and suppose you have your maximum borders in column
(B1=15, B2=30, B3=45, B4=60).

Now in C1 you can type: =A1&"-"&B1 ==> and drag down till C4 (this wil
show the periods).

Now suppose in cell E1 you have your value to be evaluated (suppose i
17 days). So E1 = 17.

In F1 if you type this formula:

=VLOOKUP(E1,A1:C4,3,TRUE)

(make sure it is TRUE in the VLOOKUP function)..... it will always sho
you the desired period, as if given TRUE Excel returns the closes
value to the one you desired but smaller than this value
 
I'd like to send you a copy of my worksheet for your review, i
attempting to put your example into place, I still do not get m
desired results
 
Ok, probs.

You can attach the file in your next message posting here, or send i
to (e-mail address removed)
 
Back
Top