Multiple Questions about adding different cells

[Redsphynx]

Hi, this is my scenario...

I have a table with 2 (more but they are irrelevant) columns:
Column # and GST

In the # Column I have numbers 1, 2, 3 etc and I manually increase it by one
every time I insert another row. In column GST on the same row as # I have a
$$ amount that I put either $$$, TBA or 'Not Costs'.

e.g. 1 = $5.00, 2 = TBA, 3 = -$6.00 4 = No Costs 5 = $2.00, 6 = No Costs etc.

Now, my questions are...
1. I have a field where I would like to know the last # used.
2. I have another field where I need to have total amount $$ (inc neg amounts)
3. I have another field where I need to know how many are TBA
4. I have one final field where I need to know how many are No Costs.

Thanks in advance for everyones help.
Redsphynx

 

[T. Valko]

1. I have a field where I would like to know the last # used.

If your #'s are sequential then the last entry will be the MAX value of the
range.

=MAX(A1:A100)

2. I need to have total amount $$ (inc neg amounts)
=SUM(B:B)

3. I need to know how many are TBA
=COUNTIF(B:B,"TBA")

4. I need to know how many are No Costs.

=COUNTIF(B:B,"No Costs")

 

[Redsphynx]

Thanks,

But I am having trouble getting the first one to work. I have the others
working great, now I just need this one.

When I enter the formula into the cell it looks like this
"=MAX(A6:A200)" and that is how it is displayed... not the number I need..

Thanks again.

 

[T. Valko]

Ok, that cell is probably formatted as text.

Select the cell in question
Got Format>Cells
Select GENERAL
OK out

With the cell still selected press function key F2 then hit ENTER.

 

[Redsphynx]

Ok, I have just worked that out, but my problem now is that the boss wants a
3 digit number in that column, so when we type 1 or 12 it will be 001 or 012.
This means he has placed an apostrophe at the start of all numbers up to
100. My question now is, for this formula to work, and for me to have the
preceding zeros, what do I need to do??

Thanks,

 

[T. Valko]

Typo:

Got Format>Cells

Should be:

Goto Format>Cells

 

[T. Valko]

for this formula to work, and for me to have the

preceding zeros, what do I need to do??

Well, if it were me I wouldn't use the apostrophe because now you have 2
different data types. You have text up to 099 and then you have numbers from
100.

I would use a custom NUMBER format of 000. If you use this format and type
in the number 1 it will display as 001 and it will still be a numeric number
then the MAX formula would work.

If you want to do as I suggested you can convert the text numbers to numeric
numbers very easily:

Select the range of cells that contain the text numbers
Goto Data>Text to Columns
Just click Finish
That should convert the text numbers to numeric numbers

Now, apply the custom number format
With the range still selected
Goto Format>Cells
Select CUSTOM
In the little box under Type:, enter 000
OK out

 

[T. Valko]

P.S.

Then apply the same custom number format to the cell that holds the MAX
formula.

 

[Redsphynx]

Thanks, that works when the =max formula is used in the same column, but when
the # is column A and the formula is in column G it doesn't work. Any ideas?