?
Solved

Excel Chart

Posted on 2014-09-15
7
Medium Priority
?
231 Views
Last Modified: 2014-11-15
No arrays please.

Attached are two screenshots of my spreadsheet. One is a shot of two columns -- one has a column for profit, and the other a column for loss. It only shows the total on every 7th day … Sundays … because for this exercise I just want one figure to show the profit or loss for the entire week Monday through Sunday.

The other shot is the graph.

How do I add a column, such that the columns in the graph aren't so spread out? Do I put a formula in a new column that searches for the first Sunday, and then +7, +14, +21 etc so that each week's result are in cells 1,2,3,4, etc?. Every year doesn't start on the same day, of course, so there must be some type of formal search for the first Sunday, then the Sundays after it.

I just want the chart columns to be next to each other, instead of with huge gaps in between the Sundays.

Thanks in advance. No arrays, please.
SnapNDrag1924.jpg
SnapNDrag1925.jpg
0
Comment
Question by:Cactus1994
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 50
ID: 40324184
Hello,

there are several ways to do this.

The easiest might be to add a column to your source data that calculates the day of the week, for example, if the date is in column A, add a formula like this in a column, starting in row 2

=TEXT(A2,"ddd")

You can now filter the data table to show only rows where the day is "Sun". Ensure that the chart has a text X axis, not a time axis. Now only the Sundays show in the chart.

screenshot
Other approaches involve building a consecutive table (without gaps) using formulas.

cheers, teylyn
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40324238
teylyn's answer is best.  However, I'll augment it by noting that you'll want to create a stacked bar chart in order to keep the bars aligned with each week ending date.  Also, set your chart area properties to "Don't move or size with cells" if you have this chart on the same sheet as your data (filtering/unfiltering would change its size, otherwise).  A gap width of 50% should work.
sample chart(I had used the WEEKDAY function to determine the ending day number and then filtered on that, but I like the TEXT function teylyn used as it is more intuitive).  

-Glenn
0
 

Author Comment

by:Cactus1994
ID: 40324402
teylyn and Glenn:

We are definitely on the right track. However, it's not possible to manually filter or sort the new columns as time goes by, as the data is a entered daily in set of values than spans several columns, over the course of a year. Other charts reference that same information in the current order of rows.

It really needs to be a static spreadsheet using formulas to return the Sunday values.

Is there a way to simply add another column, and somehow reference all the Sundays from the 365 rows I'll eventually have, with their return values in a column 52 rows long?

Thanks so much, guys.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50
ID: 40324433
OK, then Plan B: build a contiguous table with the desired values as the basis for the chart.  Columns A, B, and C have Date, Profit and Loss. Create a helper column in D with the formula

=IF(WEEKDAY(A2)=1,ROW(),"")

Copy down.

Create a table for the chart with the three columns Date, Profit and Loss, labels in row 1. In row 2 use this formula for the Date:

=INDEX(A:A,SMALL($D:$D,ROW(A1)))

Copy across for profit and loss.  Let's say this table is in columns H to J. Create three dynamic range names using the following names and formulas:

chtLabels	=Sheet1!$H$2:INDEX(Sheet1!$H:$H,MATCH(99^99,Sheet1!$H:$H,1))
loss		=OFFSET(profit,0,1)
profit		=OFFSET(chtLabels,0,1)

Open in new window


Use the dynamic range names in the stacked column chart series and axis definitions. When you enter them, you must precede the range name with the sheet name or the file name, like =Sheet1!profit

See attached file.

Enter new data as required. Extend the reach of the formula in the helper table to span 52 weeks. The chart will only ever plot the rows with data. Just ensure the X axis is a text axis.

If you ever want to plot Saturdays instead of Sundays, just change the weekday formula.

And look: No arrays! :-)
SundayChart.xlsx
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 40324440
I would set up an entirely new sheet that has the Sunday dates in one column and an indexing function (ex., VLOOKUP) in an adjacent column that retrieves the profit/loss value for that date.  This would keep the data contiguous and could be automatically updated as new data is posted in your original table.

I will say that I'm mystified as to why there are separate columns for Profit and Loss.  You can format a chart to have different colors for negative values, so only one column is really necessary.

I've attached a sample workbook with two possible charts you could use.  There is one sheet with the data ("Data"); a sheet with a static chart that references the week ending dates and profit/loss values; and a dynamic chart that will let you choose the current week ending date (cell E1) and automatically update the chart to plot up to that date.  That last chart uses a pair of dynamic range names to do this.

Note that both charts use only one set of data and plot negative values in one color (red) and positive values in another (green).

Regards,
-Glenn
EE-ChartProfitLoss.xlsx
0
 

Author Comment

by:Cactus1994
ID: 40324465
Working with both suggestions now … and Glenn, I sure wish I could format a chart to have different colors for negative values … I'm using Excel for Mac, and it's impossible.  :-(

More soon….
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40326095
I updated my example workbook to accomodate separate columns for profit and loss so that you can plot them with different colors.

-Glenn
EE-ChartProfitLoss.xlsx
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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