Solved

conditional formatting in attached file not working in excel 2007

Posted on 2014-01-06
6
262 Views
Last Modified: 2014-01-07
Hi Experts

I cannot get the trending arrows to show in excel 2007...the attached example is excel 2003...my table (see tab 5 attachment) goes across in my workbook, as opposed to downwards as per tab 5.
learn-conditional-formatting-exc.xls
0
Comment
Question by:route217
  • 2
  • 2
  • 2
6 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 39759892
I have Excel 2007 and the formatting seems to be working. Are you not seeing the arrows? Could you provide your 2007 workbook?

Flyster
0
 

Author Comment

by:route217
ID: 39759915
Hi expert

yes not seeing the arrows and...cannot up load due to restriction. ..sorry.
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39759916
route217,

If you don't see the arrows in Excel 2007, your problem is absolutely not conditional formatting, since the trending arrows are shown using In Formula Concatenate by using &.

All your conditional format do in Tab 5 is to give the text color Red, Blue, or Black.

I'm opening your file with Excel 2003, 2007, 2010, and 2013, and I don't have any problem seeing the Arrows.

Can you explain your problem a little more?
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:route217
ID: 39759943
Hi experts

I have tried to recreate the trending  arrows using new worksheet (lets ignore the one I uploaded) this is where I cannot see the trending arrows...when i open the attachment its fine. ...the problem is when I try to recreate in a new worksheet....

and apologies if the question was poorly worded.
0
 
LVL 22

Accepted Solution

by:
Flyster earned 250 total points
ID: 39760067
See attached file. It uses the same formula as the one you provided, I just modified the cell reference:

=ROUND(A2,0) & " " &  IF(A1<A2,"¿", IF(A1=A2,"¿","¿"))

The first part, =ROUND(A2,0) , just rounds the number in the reference cell. The second part,
 IF(A1<A2,"¿", IF(A1=A2,"¿","¿")), is an IF Statement that selects which symbol to show. The symbols appear through the formula, not conditional formatting. The formatting comes with the font color being changed depending on the cell value change.
TrendingArrows.xlsx
0
 
LVL 12

Assisted Solution

by:Harry Lee
Harry Lee earned 250 total points
ID: 39760575
Route217,

As Flyster says, it's about the formula that you have issue with.

You have to first understand the formula.

Please be aware that EE is not supporting the Triangle shapes and Round Dot. I have turn them into text so that you don't see the ¿ like in Flyster's post.
The & inside the formula is to help you combining the results of multiple section of a formula, and convert the whole result into a Text String.

In your formula, =ROUND(B5,0) & " " &  IF(B4<B5,"Up Pointing Triangle", IF(B4=B5,"Round Dot","Down Pointing Triangle")), the sections are
=ROUND(B5,0)
&
" "
&
IF(B4<B5,"Up Pointing Triangle", IF(B4=B5,"Round Dot","Down Pointing Triangle"))

While B5 = 549, the first section =Round(B5,0) will return 549. The second section will return " ", A Space.
Then the third section will compare B4 and B5. If B4 is smaller then B5, it will return a Up Pointing Triangle. If B4 = B5, it will return a Round Dot. Finally if B4 is not smaller than B5, and B4 is not equal to B5 (Which means B4 is lager than B5, then it will return a Down Pointing Triangle.

In your sample, B4 is 566, and B5 is 549, means B4 is larger then B5. The 3rd section will return a Down Pointing Triangle.

Combining the result of the 3 sections it will give you "549" & " " & "Down Pointing Triangle".

Combining the use of conditional formatting. If B4 is larger than B5, it will turn the text into Red, It will give you a Red Color result of 549 Down Point Triangle.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now