Excel Question

[MattR]

Mat, unless someone else gets here first!


Is there a formula to find this out. Firstly I've added a couple of things to my systems spreadsheets where it displays the bank high and low automatically by using the LARGE and SMALL formulas. What I'd like to add in the same section is the Current bank. I have a column with actual profit that I'm using the large and small formulas to read but is there one to just read the last value in the column which will of course be the current standing of the bank?

So if I had say...

120
130
110
100
140
150
130
120
120


Bank high is 150, low is 100 , but can I use a formaula to read each new bottom figure after each bet finishes to show the current bank.

[TheOldhamWhisper]

http://www.emailoffice.com/excel/arrays-bobumlas.html

Try the section on finding the bottommost cell in an array.

:)

[MattR]

Thanks Oldham, I've tried pasting that formula into the cell and changed the cells to the ones I need but it doesn't seem to produce what I want.



Another question if I can, what's the formula for finding say the "A" in a cell that has for example A1 in it. I'm sure I've used this before but I can't for the life of me recall what it was. I've just spent half hour in the microsoft help to no avail. Something along the lines of string or len or something ??

[mathare]

Another question if I can, what's the formula for finding say the "A" in a cell that has for example A1 in it. I'm sure I've used this before but I can't for the life of me recall what it was. I've just spent half hour in the microsoft help to no avail. Something along the lines of string or len or something ??

The functions you need here are probably LEFT(), RIGHT() and MID(). You may need LEN() as one of the arguments to the above to cope with variable length strings.

As for finding the last populated cell in a column, it's a doddle in VBA but a pain using formulae alone. I think you need array formulae which just get messy and at that point I switch to VBA macros. But let me mull it over a while...

[mathare]

:splapme

Finding the value of the last populated cell in a column is most easily done with VBA but the good news is that you (although I bet it's I) can create a UDF (user-defined function) to do it rather than a macro. You will either need to change the security setting in Excel to automatically enable macros or enable them every time you open the workbook.

How's that sound?

[MattR]

Thanks Mat, that sounds very good. You are right though, I wouldn't have a clue how to write a user defined function. If it's not a hassle and it's easy enough and you are offering then that's great, but don't worry if you can't, don't have time etc etc as it's not a major requirement or anything I just thought it would be handy to have on each sheet the high, low and current bank for that system just to see at a glance all in one place.


Thanks for the help with the string it's for something I'm trying out with the dogs and with their being various grades with prefixes other than A I need to filter those out and only include times in an A grade race hence the need to check the grade begins with A and not a P or S etc.

[mathare]

Thanks Mat, that sounds very good. You are right though, I wouldn't have a clue how to write a user defined function. If it's not a hassle and it's easy enough and you are offering then that's great, but don't worry if you can't, don't have time etc etc as it's not a major requirement or anything I just thought it would be handy to have on each sheet the high, low and current bank for that system just to see at a glance all in one place.

It's a two minute job. I'll post it up here shortly with instructions on how to use it

Thanks for the help with the string it's for something I'm trying out with the dogs and with their being various grades with prefixes other than A I need to filter those out and only include times in an A grade race hence the need to check the grade begins with A and not a P or S etc.

You'll just need LEFT([cell], 1) then where [cell] contains the race grade

[mathare]

Quick question Matt - does your data start in row 1 and continue without gaps until the last row? It can have a header etc at the start, I don't mind that, I just need to know where the data starts...

[MattR]

Thanks Mat.

It's usually row 11 or 12 but I can make them all the same that's not a problem, so if we say row 12 as the starting cell.

[mathare]

OK, try this for starters:

1) Open the Excel workbook and press Alt+F11 to open the macro editor
2) Right click on the VBAProjects (workbook name) at the top of the data tree you should see on the left hand side. Select Insert -> Module
3) You should now have a Module1 file under Modules in the tree. It should have opened the file as a blank module in the main window next to the tree
4) Copy and paste the code below into that window:

Function LastVal(colnum As Integer, Optional startrow As Integer = 1)

For i = startrow To 65536
    If IsEmpty(ActiveSheet.Cells(i, colnum)) = True Then
        LastVal = Cells(i - 1, colnum)
        Exit For
    End If
Next i

End Function

5) In the workbook select the cell you want to display the current bank in and enter:

=LastVal(x, y)

where x is the number of the column containing your bank figures (A=1, B=2 etc) and y is the row number on which the bank data starts.

You cell SHOULD now display the last value from the chosen column. Let me know if it doesn't....

[MattR]

It's showing the answer as 0 Mat.

[mathare]

It's showing the answer as 0 Mat.

Quickly e-mail me your workbook and let's have a quick look.

[MattR]

Ok got email if you want to remove it mat, sending it over in a minute

[MattR]

I'm no expert of course but I used to program in basic on spectrum/amiga etc and I remember enough to see what the macro is doing and it makes sense that it is showing the last cell before it finds a blank one so not sure why it is showing 0, unless it's something I've done!

[mathare]

I'm no expert of course but I used to program in basic on spectrum/amiga etc and I remember enough to see what the macro is doing and it makes sense that it is showing the last cell before it finds a blank one so not sure why it is showing 0, unless it's something I've done!

Nope, schoolboy error on the part of the author.

Never rename a written and tested function on a whim when posting it up on a forum

Edit: I have corrected the code above now too

[mathare]

I'm no expert of course but I used to program in basic on spectrum/amiga etc and I remember enough to see what the macro is doing

In which case you should find VBA coding no problem at all. It's just a case of learning what keywords/functions exist in the language and what the syntax for them is (and the in-built help is actually good for that sort of stuff) and you're away. If you've programmed before you could write VBA easily enough, should you so desire.

[MattR]

Yeah, I keep meaning to get around to giving it a go. I got as far as getting a vba excel book.

[MattR]

Don't tell Vegy but I have to spread rep around before giving you some!

[alfazzr1100]

If you can't get the User Defined Function idea to work you could put in a column next to the ones you have that is some thing like this, assuming say your running bank is in cell k23 for instance, then in column L put:

=If(k24=0,k23,0) and copy it up and down the whole column.

Then L23 will equal your current bank and all other values in this column will be zero. To find your current bank you can then sum column L.

[MattR]

Thanks alfazzr1100 :)

[eastyorkshireracing]

Hi Matt,

I would say that the best way to find the end number in a column is to use the INDEX & COUNTIFS formulas like so.....

example:

=INDEX(A:A,COUNTIFS(A:A,">=0"))

Assuming that column A contains your data and the data is not a negative (if it is then just ammend the ">=0" part to any neg number you like) :)

Hope this helps.

Gordon

[MattR]

Thanks Gordon :)