Help me figure out how to go about making Excel magic

January 6, 2009 8:43 PM

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 comments total)

I don't think you need a macro. You could just use an embedded Absolute Value function with an if/then statement (within another if/then statement). It's not necessarily pretty, but it works, and the logic is simple.

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

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

@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

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

Ahh, good point and good fix.
posted by Pants! at 8:43 AM on January 7