Log In
Register
Home
July 15, 2020
Offline
I have a simple line graph and have highlighted the period which contains the highest value. I used the formula, "=IF(MAX($B$5:$B$24)=B5,B5,NA())" to identify the maximum point, and the formula, "=ISNUMBER(C5)" to identify the period to shade.
How can I rewrite the 1st formula to identify the 3 highest numbers in column B, so that the shade helper column will then include all three periods?
Thank you.
See Answer
July 16, 2010
Offline
Hi Tom,
You can use this formula in cell C5 then copy down (if you're not on 365 you need to enter the formula with CTRL+SHIFT+ENTER):
=IF(MATCH(B5,LARGE($B$5:$B$24,{1;2;3}),0),B5,NA())
Mynda
Answers Post
July 15, 2020
Offline
Thank you Mynda, that worked perfectly!
July 15, 2020
Offline
A quick follow-up question: Regarding the shaded periods, is there a way to have the shading go below the x-axis so that it includes the time period label?
July 16, 2010
Offline
No! Sorry. You cannot format the axis labels area.
Mynda
Active Member
May 24, 2019
Offline
Hi Mynda and Tom,
This would be your workaround ( I am attaching the file also)
Basically, you have to add two columns, one for positioning the label and another one with blanks (double quotes) to just show that. So in the label it doesn´t show anything. Once the labels are put, at first you will see a 0 (cero) go to the label options and switch it off (value) but first, activate the value from cells option and then select the dummy column with blanks. Then, you resize the label area (or label box) with height: 0.66 and 0.81 width and lastly, apply the same color of the bars.
Hope it works for you.
The following users say thank you to Microsoft Excel Recalc Or Die for this useful post:
Mynda TreacyActive Member
May 24, 2019
Offline
Hi Tom,
My workaround above is a bit "static", case in the future you need it to be more dynamic, scatter plots is the way to go.
with this type of chart, there are many possibilities for "hacking" it´s options. Below it´s something I recently developed where the requirement was a bit similar to yours. I will attach the file case you want to review it.
Regards,
At Mynda, I got this message: "015_highlighting_axis_item.xlsx: Error 106 - Upload file size exceeds maximum allowed size"
Would it be ok to share an external link for downloading the file ?
July 16, 2010
Offline
Sure, happy for you to share an external link. Nice solution!
Active Member
May 24, 2019
Offline
Hi Mynda and everyone,
Sorry for the delay, here it´s the link to download the workbook: https://1drv.ms/x/s!Am3AC3QcIM.....A?e=SfUFZs
related to the Gif image above. Note: this workbook was done using Excel´s new functions: # dynamic arrays #. It can be done using Excel tables and cell ranges as well.
Take care everyone.
The following users say thank you to Microsoft Excel Recalc Or Die for this useful post:
Mynda TreacyJuly 15, 2020
Offline
Another follow-up: When I have the columns highlighted, and combine it with a line graph, it works great. But if I want to have one of the lines to be shown as a column chart, any columns in the shaded area are behind the shading so they are not visible and I cannot get them to display on top of the shading. Mynda, do you have a solution for that?
Thanks,
Tom
1 Guest(s)
