Formula create border to separate categories based on values + add text [SOLVED]

[jglo]

Hi!

I posted last night about how to use conditional formatting + formulas to add text (thank you Hui). However, I have run into some tables that have 3 categories of information and I can't figure out how to accomplish what I need to:


https://dl.dropboxusercontent.com/u/107829746/EXCEL/sorting%20spreadsheet.xlsx


For file above,this is a sample of a table - each month the data changes and is resorted (there are 50 tables). It is currently done manually. I recorded a macro to do all of the sorting. In this case, after I do the sort, I need to add two lines within the table:


1st line will separate anything over 100, with text(is in a merged cell currently) that lines up to the line "+100"


2nd line separates those values that are above the average (77) from those that are below, with a text that indicates Avg = TOTAL


Any help that you can provide would be very much appreciaed.


Thanks!

Alice

 

[jglo]

FYI, this post contains my original query

http://chandoo.org/forums/topic/how-to-merge-cells-and-insert-data-based-on-change-in-data-in-adjacent-column

 

[SirJB7]

Hi, jglo!


Try doing this:

a) Select range B2:B23.

b) Start tab, Style group, Conditional Formatting icon, New Rule, Use Formula.

c) Formula:

=O(Y($C2>100;$C3<=100);Y($C2>PROMEDIO($C$2:$C$23);$C3<=PROMEDIO($C$2:$C$23))) -----> in english: =OR(AND($C2>100,$C3<=100),AND($C2>AVERAGE($C$2:$C$23),$C3<=AVERAGE($C$2:$C$23)))

d) Set format to Border, Lower, Underline.

e) Apply to range $B$3:$D$23 (it'd be automatically done as it's selected, but check it).


But I found an issue regarding the average value, cell C25 has a constant value 77 where if you use a formula like:

=PROMEDIO(C2:C23) -----> in english: =AVERAGE(C2:C23)

you get a value of 126. Am I wrong? The solution posted is with this formula and not with the constant value.


Regards!

 

[jglo]

Thank you!!


You are right, the average does not equal the total column,it needs to relate to the cell w/ the total.

 

[SirJB7]

Hi, jglo!

Just put in cell C25 the average formula.

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!


EDITED


PS: It's missing the add text part, isn't it? But that couldn't be done just with CF, it'll include some VBA code.

 

[jglo]

Thank you - the lines are now drawing via conditional formatting. I will now try to see if I can add the text. Thanks for your help!

 

[SirJB7]

Hi, jglo!


Place this formula in E2 and copy it down thru E23:

=SI(Y(C2>100;C3<=100);A$32;SI(Y(C2>C$25;C3<=C$25);"Avg="&REDONDEAR(C$25;0);"")) -----> in english: =IF(AND(C2>100,C3<=100),A$32,IF(AND(C2>C$25,C3<=C$25),"Avg="&ROUND(C$25,0),""))


Now I think it's done, isn't it?


Regards!

 

[jglo]

This is fantastic!

Is there any way to have these either: Merge with the cell below so they are centered with the line.

OR

I would be happy with AVG= being in one cell and 77 being in the cell below so they line up.


Thank you so much, I was trying to go about this is a very backwards way!

 

[SirJB7]

Hi, jglo!


Absolutely no to merged cells. Yes for the average and not for the +100.

=SI(Y(C2>100;C3<=100);A$32;SI(Y(C2>C$25;C3<=C$25);"Avg=";SI(Y(C1>C$25;C2<=C$25);REDONDEAR(C$25;0);""))) -----> in english: =IF(AND(C2>100,C3<=100),A$32,IF(AND(C2>C$25,C3<=C$25),"Avg=",IF(AND(C1>C$25,C2<=C$25),ROUND(C$25,0),"")))


Maybe this helps with the 100 threshold:

=SI(Y(C2>100;C3<=100);A$32;SI(Y(C1>100;C2<=100);A$33;SI(Y(C2>C$25;C3<=C$25);"Avg=";SI(Y(C1>C$25;C2<=C$25);REDONDEAR(C$25;0);"")))) -----> in english: =IF(AND(C2>100,C3<=100),A$32,IF(AND(C1>100,C2<=100),A$33,IF(AND(C2>C$25,C3<=C$25),"Avg=",IF(AND(C1>C$25,C2<=C$25),ROUND(C$25,0),""))))

And place a ="-100" in cell A33.


Regards!

 

[jglo]

This works perfectly! Thank you sooooooooooo much!