Fucking Excel

Chubbly

So fuck Excel. I am trying to do draw two lines on Excel. I have provided a visual demonstration of what I am trying to achieve:



I have the grey parts graphed. Now I want to add the two coloured lines. The x-axis is discrete. As is the y-axis. Now, the two values I want to add are at 1.66 and 8 on the x-axis on the same scale. Their y value is effectively infinite as I just want to show the average and the calculated (these are the labels for the two lines) as they compare to each individual set.

How do I do this?

This is Excel 2007 and I hate excel.

ronya

Why is their y value effectively infinite? Why not just draw a red and blue line using Insert Shape?

Teslan26

Chubbly wrote: »

I hate excel.

Tinuz

You could always just insert a second graph in there with the properties of those lines.

ProPatriaMori

You can graph another couple series on the same chart using a different type of graph. The easiest way I have done this is right-clicking on the series name in the legend and using "change series chart type." So you could use two extra series in X,Y scatterplots to put your lines on there.

Chubbly

The scatter plot graph might work. Excel, though, likes to be a huge bitch and tell me it can't have both types of graphs on the chart.

As to just drawing the line, I want it to be a part of the graph so if I move and resize it, it's position isn't lost.

ProPatriaMori

I have done this before (just now in fact to be sure it works--I even drew a rectangle for no good reason) so hopefully you should not have problems.

DJ-99

Chubbly wrote: »

The scatter plot graph might work. Excel, though, likes to be a huge bitch and tell me it can't have both types of graphs on the chart.

As to just drawing the line, I want it to be a part of the graph so if I move and resize it, it's position isn't lost.

If you feel comfortable making the graph in PowerPoint, which will look better anyways, you can group objects together, so they all move together. I'm not sure if Excel has that functionality, but I doubt it.

PowerPoint is generally better for making graphs no matter what, I think.

Tinuz

DJ-99 wrote: »

Chubbly wrote: »

The scatter plot graph might work. Excel, though, likes to be a huge bitch and tell me it can't have both types of graphs on the chart.

As to just drawing the line, I want it to be a part of the graph so if I move and resize it, it's position isn't lost.

If you feel comfortable making the graph in PowerPoint, which will look better anyways, you can group objects together, so they all move together. I'm not sure if Excel has that functionality, but I doubt it.

PowerPoint is generally better for making graphs no matter what, I think.

Depends on the purpose. But you can make several graphs (of all the features and lines you want) in excel, and then paste them into PP to group them. The resulting slide can be used in word docs and whatnot.

EDIT: Before I forget: Excel sucks...really, it sucks for almost everything except the simplest, most mundane tasks where any sort of excellence isn't required.

RocketScience

I've done something similar to this in the past. PPM is on the right track, but I'm also vague on the details.

Daenris

Here it is in Excel... kind of had to kludge around it and there are definitely better graphing program options... but it works. I can tell you how or just send you the excel file I used, whichever you want.



edit: Basically, I have the original graph as one series in a scatterplot graph, but expanded to take up the entire number:

0 10
0.05 10
0.10 10
...
1 10
1.05 6
1.10 6
...
2 6
2.05 1
2.10 1
...
3 1
...
11.05 1
11.10 1
...
12 1

Then give them negative Y errors bars of 100% size, beef the error bars up to the heaviest weight, and remove the point marks. Then have 2 more series, with each of the special lines you need:

1.66 100
8 100

And beef those error bars up the same way, then just set the Y-scale on the graph to only display to 11. Then one more empty series just to add the legend if you need to.

Septus

Teslan26 wrote: »

Chubbly wrote: »

I hate excel.

Yes. I was so infuriated to find out that I couldn't just simply create a break in the axes of a graph, to skip large spaces empty of data points, it was this whole ridiculous drawn out workaround.

The problem I'd have with this solution here, is that sure you can group the objects together of the chart in PP, but if you ever want to change the data, like add another bar graph and then rescale your x-axis min and max, you have to move the lines again.

Sir Carcass

I can't see your image, but if it's like the problem I was having, it was fixed by not using Line Graph, but XY Scatter. I was trying to have 2 sets of data in the same graph (one began where the other one stopped), but the Line Graph was having fits. XY Scatter fixed it.

tsmvengy

ronya wrote: »

Why is their y value effectively infinite? Why not just draw a red and blue line using Insert Shape?

Srsly.

Edcrab

So, sorry to hijack this thread, but speaking of Excel my sister has made the mistake of turning to me to explain how to do something in time for an important presentation, and sadly I don't grasp the program nearly as well as I used to

What's the option in Excel to make it that when sorting entries in the spreadsheet, each entry has a bold header, added automatically whenever you add data?

I.e., with a selection of surnames:

Roberts
Roberts D K 5
Roberts K M 6
Carter
Carter W E 3
James
James A B 3
James C J 8
James K J 4

And, of course, if I was to add a entry with the surname of "Smith", that would gain its own header in the filter. It's really simple and basic and I can't for the life of me remember how to do it. Help please!

Edcrab

Oh, and it's for the 2003 edition or 2007. Which is problematic, come to think of it, since I currently only have my old 2002 version installed and might find it hard to check things Thanks.

BlochWave

I'm not sure I've ever seen a single option for that. You can go to data and make a list, but I'm not sure how to flag multiple headers. You can fudge it by applying this conditional formatting formula to the entire list: =IF(LEN(TRIM(D23))=0,0,LEN(TRIM(D23))-LEN(SUBSTITUTE(D23," ",""))+1) < 2 with the format set to bold. Cells with a single word in the list will be bold

Excel is an exceptional and powerful computational tool and can do just about any desired task so long as you have a rudimentary knowledge of vba or access to google. I use it often for sensitivity and risk analysis at work using Crystal Ball, an excel Monte Carlo add-in. I also use it to independently calculate statistics on data obtained from other tools (which are less trustworthy), and in one instance I recreated Crystal Ball's rudimentary functionality using Excel's vba and its own random number generator for a customer who needed a simple Monte Carlo analysis but didn't have the software.

And in some instances it can be good for sorting data, but a database tool would be preferable, like the preceding poster's question. Excel and Access are ubiquitous in the engineering world. An entire class on just how to use Excel properly would have been one of my most useful classes in college if it existed.

In any event, I think the error bars trick is the classic solution but I don't see what the issue is with just manually drawing them in, in either Excel or Powerpoint.

Edcrab

Thanks a ton BlochWave, that recreated the effect, at least enough for the purposes of the presentation

In yet another question, does anyone know how to transpose (flip columns/rows) a spreadsheet with 20k+ entries? When I try to use the Paste-Special menu, which works fine for small sets, it just spouts bullshit about selecting single cells (which I'm doing) or selecting a rectangle of equal size (which is beyond me when there's so many entries).

EDIT: Hmm, I think I must be hitting the column limit and then some. No wonder it won't work. Time to tell the workplace to get a proper database already.