Prev: CONDITIONAL FORMATTING & NEGATIVE VALUES
Next: check if any values on row
From: JoeM on 28 Feb 2010 15:19
it's been a long day and i know this is simple to do, but i think i am burned
out:

=VLOOKUP(J2,{0,"0-30";30,"60";61,"120";121,"180";181,"300";241,"300";301,"365+"},2)

need to be able to carry over to my pivot

joe
From: Max on 1 Mar 2010 18:04
As-is, your formula looks ok in creating the "buckets" text labels col for
your pivoting. It returns the required text label ("bucket descript")
depending on the numerical values in J2 down. Maybe just add a simple front
IF check for blank cells and do an "add zero" to the lookup values in J2 down
(J2+0, instead of J2) to coerce any text numbers in col J to real numbers:
=IF(J2="","",VLOOKUP(J2+0,{0,"0-30";30,"60";61,"120";121,"180";181,"300";241,"300";301,"365+"},2))
Joy? hit YES below
--
Max
Singapore
---
"JoeM" wrote:
> it's been a long day and i know this is simple to do, but i think i am burned
> out:
>
> =VLOOKUP(J2,{0,"0-30";30,"60";61,"120";121,"180";181,"300";241,"300";301,"365+"},2)
>
> need to be able to carry over to my pivot

 | 
Pages: 1
Prev: CONDITIONAL FORMATTING & NEGATIVE VALUES
Next: check if any values on row