Excel/Open office spreadsheet question

S

sdy284

Gawd
Joined
Dec 6, 2006
Messages
601
ok, so I have a spread sheet with a few different sheets. On one sheet, I want to have a graph that pulls data from the other sheets. This by itself is easy. But what i'd like to do is have the graph automatically update when i add new data sheets. All of the data sheets are the exact same format (meaning data locations is the same)

whats the best way to accomplish this?

and part two of this... (this is for scrabble btw)

2dklhg4.jpg


I'm trying to figure out an automatic way for the spreadsheet to calculate the player bonus. In scrabble, if you use all of your tiles, then you get however many "points" are left of your opponents remaining tiles.

You can see my if statement on the screenshot, however the function only returns "true" or "false" and i would like it to display the actual value of H4. Which would be equal to G3 if G4=0 or the value for H4 would be 0 if G4 didn't = 0

*note the values in the player bonus column are typed in, and not the result of a function
 
The functions only return true or false because you are telling it to only return true or false. G4=0 is the logical test. If the value is true, it returns H4=G3, which seems to be true from your picture (1=1). If G4=0 is false, H4=0 is returned, which seems to be false because H4 is 1. You don't want to have an expression evaluated in your return values. Try returning H4 or G3 or whatever instead.
 
Snowknight26 said:
The functions only return true or false because you are telling it to only return true or false. G4=0 is the logical test. If the value is true, it returns H4=G3, which seems to be true from your picture (1=1). If G4=0 is false, H4=0 is returned, which seems to be false because H4 is 1. You don't want to have an expression evaluated in your return values. Try returning H4 or G3 or whatever instead.
Click to expand...

care to elaborate? sorry i'm a if-function noob with excel

and any input on my first question?
 
It should look like this:
Code: 
=IF(G4=0,G3,0)

When you use the equals signs you are evaluating more things which gives you a true/false answer. The way I put it you are displaying the values (not evaluating them).

For your first question I would need to see more on what the graph is showing and what the individual sheets look like.
 
to sum across multiple sheets.

I usually use a start and end sheet, e.g. I have a workbook with the following worksheets present

graphsheet
firstsheet
game1sheet
game2sheet
...
lastsheet

Then, on the graphsheet, add the graph (using data from graphsheet) and you enter the equation to sum across the other sheets.

Example: =sum(firstsheet:lastsheet!a2)
This adds up all A2 cells from firstsheet through lastsheet.

As long as you add new worksheets between the firstsheet and lastsheet, the equation does not have to change.

About your if check. A simple example,
=if(a>b;34,89)

this means that if a > b is true, then evaluate the second part and return it. In this case, that would be 34. If a >b is not true, then evaluate the third part and return it, i.e. 89.

=if(g4 = 0;h4=g3;h4=0)

This means that if g4 = 0, then return the result of the logical comparison of h4=g3, or true/false. Likewise, if g4 <> 0, then return the result of the logical comparison of h4=0, once again, true/false.
 
You must log in or register to reply here.
Back
Top