Excel chart

[boofhead]

I am trying to make a weight and balance chart using Excel (line chart). I need to display the points in series for the empty weight, landing weight and takeoff weight.
My problem is that I cannot make the series show if the empty weight is a calculation result. The weight and cg for the empty weight is a result of a Choose function, using various types of configurations, thus it is a calculated value. I have seen that if I use a fixed value for the X axis instead of a calculated value, the line draws, but if not, the line does not show at all. The other values of the line are also calculated values, depending on the fuel on board at takeoff and landing, but they don't seem to be a problem, just the one given as a result of the Choose function and just the one axis.
I have tried to make the field a General display as well as a number, no change in the result. I have tried to make the value a simple calculation, with another field being the Choose result and the field I am trying to use be an = value, to no avail. Something I am missing, since I know this can be done and I have done it before, but I would appreciate help.
I need to make this work in Excel version 2003, because the people using it will not have access to anything later than that.

[Spurlash2]

Might be worth posting on Windows Secrets Lounge (http://windowssecrets.com/forums/forumdisplay.php/15-Spreadsheets). You will be able to upload your spreadsheet with your query for the experts to have a play with.

PS. Are you able to move the results of the calculations to another part of the spreadsheet and have them formatted as 'ordinary' numbers? By that I mean, can you do a Paste Link, reformat, then graph on those figures?

PPS. In fact, if Paste Link doesn't do it, copy the results, Paste Special|Values. This will remove the sums in the cell and just give a plain number.

[Genghis the Engineer]

Hard to be sure from your description, but I suspect that you may be using the wrong graph plotting route. Make sure you are using X-Y.

Also, I can often save a lot of aggro if you copy ALL of your data into a couple of discrete columns off to the right somewhere, and plot from them. Then all Excel sees is numbers copied from another cell.

G

[boofhead]

I needed three data points for the series graph. They were the values from the weight and balance calculation for weight and cg at Empty, Landing and Takeoff. For some reason the values for landing and takeoff worked, but when I added the empty weight the line disappeared. If I put in a fixed value for empty weight cg, it worked, but the line was only accurate for one condition that way, any value of cg that did not agree with the empty weight cg that I chose would be wrong and the graph was wrong too.
The various empty weights were dependent on the airplane configuration, and those configurations were vastly different, depending on the equipment carried at the time. I have a Choose or If function to decide what the actual empty weight and balance would be, and the actual weight and balance calculations work well, giving good values. My problem is displaying the result on a chart.
I figured it was something to do with the way the calculation was done, and the result was not clean enough to use. I tried remoting the result, and using a copycat cell to make the graph but it did not work. I needed a way to simplify the calculation. I tried putting the actual Choose or If calculation in the cells used for the series graph, copying the original cell, but that only partly worked. It satisfied the Y axis but not the X axis.
I finally got something of a workaround. I made up a calculation by referring the cell that had the data with a mythical calc. In this case the cells that had the calculated data were C31 and D31. So I told the data set that needed to know the value of C31 (and D31 in its turn) that it was to use C31 with a calculation included. Thus the cell I was using to make the data set had the function "=C31+0". And it worked. Now the graph displays properly. Don't have any idea why.
It has worked so far on several computers using Excel 2003 and 2007 and 2010. I will need to see if it works with Open Office. Should be fun.
Until the next problem.

[ExGrunt]

Hi Boofhead,

I am not a pilot, so I am having difficulty grasping what your chart is trying to show.

However, I do know a bit about excel. I think your problem is the 'choose' function. Excel has several different data types: eg numeric, text.

My suspicion is that the choose function is returning a string or text data type, so that when you try to chart it it interprets it as a Null value - hence the line disappears.

What the "=C31+0" is doing is forcing it into numeric data type, and so it is possible to chart the result.

HTH

EG

[Spurlash2]

boofhead,

Did you try the Copy|Paste Special|Values I mentioned in post No2?

[boofhead]

No, I am not smart enough to understand what that meant.

I did format the values as numbers, but that alone did not do the trick. It was in fact a text function as you said so the result was being read as such. Thanks for the explanation, I might be able to get it working normally now.

Thanks for the help.

[ExGrunt]

BH,

I did format the values as numbers, but that alone did not do the trick.

FYI: Formatting cells as numbers will not solve a data type problem because the number formatting has four elements: 'positive format';'negative format';'zero format';'text format'

So a number format will happily display text. What you usually notice is that numbers are right aligned in a cell and text is left aligned, but this will not happen where the cells have been manually formatted eg centred.

BTW: CHOOSE is considered a bit of a legacy function held over for compatibility with older versions of Excel. The more modern approach is to use a combination of the INDEX and MATCH functions with a data table. However, given your expressed level of skill I would just stick with what works for you.

HTH

EG

[boofhead]

Choose only allows 7 items to be compared, which is limiting. I could use Index or Match to better effect? Is it available in 2003 version of Excel?

[Spurlash2]

In a word; Yes.

If you like, I can send some worked examples and also a spreadsheet, that I use everyday, which utilises the Index and Match functions.

[ExGrunt]

BH,

Yes, I would use INDEX and MATCH - which are available in Excel 2003.

The main caveat is that the search values MUST BE unique, otherwise MATCH will just return the first matching value it encounters.

Here is a short example:

In cells C2:C5 enter 1,2,3,4.

In cells D2:D5 enter Apple, Bananna, Orange, Pear.

In cell C8 enter 1.

In cell D8 enter: =INDEX(D2:D5,MATCH(C8,C2:C5,0),1)

Where:

D2:D5 is the data array
C8 is the search value
C2:C5 is the search array
0 sets the search to an exact match
1 returns the data from the first column in the data array

You will see that by changing the value in C8 the corresponding value is returned in D8.

The above references are relative references and so will change when the formula is copied to other cells. So it is better practice to use absolute references (ie $D$2:$D$5) or (my preference) named ranges for the data and search arrays, but that is a level of complexity that I will leave out of this example.


HTH

EG