Conditional formatting using Icon sets (comparing data to data in array)

Hi Folks,

I already know you can't specify an array in the Icon area in conditional formatting. I'd like the arrow indicators with the data values and not in a separate column (which is pretty easy). The purpose is I have atleast 6 different columns worth of data I'd like a up down comparison and creating 6 different columns just for the indicators defeats the purpose of having a summary table.

Array = (B2:C11)
Task = compare values across columns in same row (e.g. C2 with B2)
Requirement = Arrow indicators in column C with the data values

I've included the ideal output in cell ref C2 in attached example

I've trawled the forums and found solutions that require creating a separate column (and VB macros). Any possible way this can be done without macros?

PS - First time I'm posting on any excel forum so please excuse the verbosity

kind regards, Dims

EXAMPLE.xlsx

Originally Posted by FDibbins

@Anka, i just looked at you're solution. you have nothing in that file except the OP's orginal data?

Really? I think I made a mistake when I upload .

Hi Anka,

Thanks for giving this a shot. It's still off from what I'm expecting but I think that's my fault for not clarifying it.

I've included another column (D) with the difference and the expected output. But I'd like to avoid creating column D and include the indicators in C. The expanded the values after decimal.

Basically:
1) Up Arrow if (C2-B2)>0
2) Level Arrow if (C2-B2)=0
3) Down Arrow if (C2-B2)<0

Example.xlsx

Can you use cell color (green=increase, yellow=no change, red=decrease) or does it have to be an Icon set?

Or maybe this example will help you

See here.

I don't think you can do what you describe with Excel's current Cond Format rules. Will a colored cell version work - comparing year b to year a?

Hey Anka,

Man, your English is no problem with me. I understand you well and, more importantly, you're really trying to help. I'm grateful

Your formula is actually pretty neat but is a problem if I try to apply any calculations because of the special character + number format. Check out column F in attachment and you'll see what I mean.

Example.xlsx

If this were possible using the icons in conditional formatting you'd have no problem calculating the numbers.

Steve - I think your solution's the best way about it given the default settings. Had to settle this for a work related problem today and ended up building additional columns with the math but with indicator only (no C2-B2 values).

Thanks loads guys. Really appreciate the help.

kind regards, dims

Hi Kevin,

Thanks for this. Embarassed to say I missed it because I was looking at the more complicated bit and didn't see the value add to Anka's solution, which you spotted.

Marked it solved.

Season's greetings folks. Hope you guys and your families have a wonderful time.

kind regards, Dims

Hi darth.dims

Thank you for the feed back.

Kevin