January 6, 2009 8:43 PM Subscribe

How can I have Excel generate a new column with the data I need using certain conditions and pulling data from a pivot table?

I have thousands of items and 3 columns of values assigned to each item in a pivot table. I want to say "if column B and C values are identical then put that value in new cell" I also want to say "if values in B and C are not identical then pick whichever of the two is closest to the value of A and put that value in new cell"

Any ideas on how I can do this? I'm relatively comfortable with excel and pivot tables but I've never written a macro before and don't know where to start.

Thanks, all!
posted by ttyn to Computers & Internet (5 answers total) 2 users marked this as a favorite

I have thousands of items and 3 columns of values assigned to each item in a pivot table. I want to say "if column B and C values are identical then put that value in new cell" I also want to say "if values in B and C are not identical then pick whichever of the two is closest to the value of A and put that value in new cell"

Any ideas on how I can do this? I'm relatively comfortable with excel and pivot tables but I've never written a macro before and don't know where to start.

Thanks, all!

I'm not sure if above will work because you want to compare B and C, not A and B, as far as I can tell. Try this:

=IF(B1=C1,B1,MIN(ABS(C1-A1),ABS(B1-A1)))

posted by Pants! at 10:01 PM on January 6, 2009

=IF(B1=C1,B1,MIN(ABS(C1-A1),ABS(B1-A1)))

posted by Pants! at 10:01 PM on January 6, 2009

@Pants!

The value if false results in the actual absolute value sum being placed in the cell rather the value of the existing number that is closest to A1.

If A1 = 2, B1=2, C1=0, then I want the value of B1 to hold true for A1. Currently the formula generates a value of 0 because the difference of B1-A1 is 0.

Also I forgot to add that if the absolute value of the difference between A1 and B1 is the same as A1 and C1, then it may as well keep the value of A1 without changing anything.

posted by ttyn at 6:52 AM on January 7, 2009

The value if false results in the actual absolute value sum being placed in the cell rather the value of the existing number that is closest to A1.

If A1 = 2, B1=2, C1=0, then I want the value of B1 to hold true for A1. Currently the formula generates a value of 0 because the difference of B1-A1 is 0.

Also I forgot to add that if the absolute value of the difference between A1 and B1 is the same as A1 and C1, then it may as well keep the value of A1 without changing anything.

posted by ttyn at 6:52 AM on January 7, 2009

Thanks to both of you setting me on the right path!

If anyone is curious, I got it to work using this:

=IF(B1<>C1,IF(ABS(B1-A1) < ABS(C1-A1),B1,C1),B1)

posted by ttyn at 8:10 AM on January 7, 2009

If anyone is curious, I got it to work using this:

=IF(B1<>C1,IF(ABS(B1-A1) < ABS(C1-A1),B1,C1),B1)

posted by ttyn at 8:10 AM on January 7, 2009

This thread is closed to new comments.

See if this works for you:

=IF(A1=B1,A1,(IF((IMABS((B1-A1))>(IMABS((C1-A1)))),C1,B1)))

I'm assuming you know how to add the "$" so you can copy the formula down the column.

posted by boeing82 at 9:35 PM on January 6, 2009