TAbles in Excel

[LisaGreen]

Hi everyone.... I have a problem with er ... tables.

I have a table of data about "bets" with cashiers in a shop. It's a game we play with the cashiers... we'll say the final cost will be say 23 and as many cashiers as want to will chip in with a guess.
I then write that down on the receipt, take it home and put it into an excel table.
It's useful as well because it keeps track of how much we spend there.

And there's the problem.
If say three cashiers give their guess and I give mine then there are three bets.
If I then add up the column that lists the actual amount the purchases come to then I'll get three times the actual amount spent!

The column headings for the table are....
Date/Cashier Name/Cashier Guess/Customer Name/Customer Guess/Actual Amount/Cashier Diff/Customer Diff/Middle/Winner Name/Loser Name/Bet Number

Without tables and in an extra column... I'd ....
1) Make sure the data is sorted on Date and then Actual Amount.
2) Check the date column, and to make sure the Actual amount column with the amount above it.
3) If both of 2 are true then I'd put zero / blank in the extra column.
4) If either of 2 are false, put the Actual amount in the extra column.
5) SUM The extra column.

I just love using structured references but cannot find a way to reference the row above with them!!!!

Oh... and something I can't figure out.
If I enter the formula "=ROW(tblBets[Bet Number])" anywhere outside of the table I get 14. Driving me loopy!!

Can anyone help here please?

TIA
Lisa

[HansV]

About your last question: try =ROW(tblBets[@Bet Number]) i.e. with an @ before the field name.

[HansV]

You can use =OFFSET([@Actual Amount],-1,0) to refer to the actual amount in the row above.

[LisaGreen]

Hans!!!!

Thank you so much for answering so quickly on a Sunday evening as well!! Hope you're okay!

ROW(tblBets[@Bet Number])

Returns the excel sheet row number as long as the formula is within the row range of the table. If it's for example, below the table, it returns the #VALUE! error.

I'm just getting hair pulling out frustrated why without the @ it returns the same number all over the sheet!! What's it doing!!??

=OFFSET([@Actual Amount],-1,0)
Thank you! Yes that works well... but... do you think there's a non volatile formula at all?

Lisa

[HansV]

{@Field] tells Excel to look at Field in the same row as the cell with the formula. That's why ROW(tblBets[@Bet Number]) only works within the row range of the table.

I don't know of a non-volatile alternative. Someone else?

[rory]

I think you'd have to use something like:
=INDEX(tblBets[[#All],[Actual Amount]],ROW([@[Actual Amount]])-1)
and you'd need to include an offset if the table doesn't start in row 1.

Much easier to skip the structured reference, IMO.

[LisaGreen]

Thank you Rory!!

I take your point but I just like the SRefs. A style difference.

I thought I'd read that msoft listed INDEX as a volatile function but there are lots of examples of Index and MAtch being used to avoid te volatile thingies.

Lisa

[HansV]

OFFSET is volatile, INDEX isn't - see Volatile Excel Functions.

[rory]

INDEX is semi-volatile (recalcs at workbook open) if you use the range:INDEX(range) format, but otherwise not.

[LisaGreen]

I knew I'd read it somewhere!!!

http://www.excelforum.com/excel-general ... -down.html" onclick="window.open(this.href);return false;

I think 3rd post down.

DOn't shout at me if it's not true please!!! I just remembered reading it.

Lisa

Oh... I've just re read the page you sent Hans... it says a similar thing there... that some functions are documented as volatile but aren't ... apologies.

Lisa

Good info Rory!!

Thanks guys!

[LisaGreen]

For information.....

The final result was....

=IF([@[Bet Number]]=1,[@[Actual Amount]],IF(AND([@Date]=OFFSET([@Date],-1,0),[@[Actual Amount]]=OFFSET([@[Actual Amount]],-1,0)),"",[@[Actual Amount]]))

Lisa

[Rudi]

Tx for the feedback...

[LisaGreen]

I'm still a bit confused as it happens.... I've just posted another query about tables in fact.

In the formula I've posted... which works and I'm quite happy with it even if it is volatile!... Why are some @ signs for column names inside the square brackets and some outside???

It's a mystery to me.

And an extra g&t token to the one who first finds where that song quote is from!!!

TIA
Lisa

[HansV]

The @ is always inside the square brackets, but the field name must be enclosed in (an extra pair of) square brackets if it contains spaces or punctuation. Thus:

[@Date] since Date is a single word
[@[Actual Amount]] since Actual Amount contains a space

[Rudi]

It's a mystery to me... who first finds where that song quote is from!!!

Toyah - [@[It's a mystery]]