is there a formula to get rid of decimals in an excel cell?

[cantona_007]

Hi,

I'm really new to excel (2007). But i've managed to create a worksheet that tracks payments for me and my coworkers. I made it such that it tracks the outcome of post-dated payments as well. i used one of the conditional formatting options (3 symbols circled)... so if there's a payment posting today... i enter 1, and then a checkmark is displayed right next to it, if its one for a future date i enter 0.5 and then an exclamation mark is displayed, and if a payment doesnt go thru i enter 0 and then a red x mark is displayed.

Now, the problem is i also have a running total that keeps track of how many payments you've had for the month. only that when i enter the 0.5 it adds to your total and i dont want this. is there a formula that would make the running total ignore the 0.5 and only add the 1 to its total??


Thank you so much.

 

[circledchicken]

Hi,

I'm don't think I completely understand your question, but if your trying to add only the 1's in a range (e.g. A1 to A5), you can try something like this:

=SUMIF(A1:A5,1)

 

[marka87uk]

You can use COUNTIF to only count the occurences of 1. e.g.

=COUNTIF(A:A,1)

 

[cantona_007]

Yes. this is exactly what i need to add only the # 1 in the running total! thank you soooooo much!!!! jheeze!! i've been troubling myself with trunc, rounddown roundup formulae for like a week!!! to no avail.... thanks so much... this has really really helped!!!


thanks alot to you both!

 

[cantona_007]

sorry one last thing...
How do i protect the worksheet so other users wont be able to edit or modify anything...just like a read-only, or i suppose input-only kinda thing??


thanks so much.

 

[marka87uk]

Tools > Protection > Protect Sheet

 

[cantona_007]

ahhh... many thanks!!!! cheers!

 

[cantona_007]

one final thing (i promise)... is there a way i can lock the scroll bar to a specific range like say from A1:P70...this way the scroll bar wont go beyond that range??


thanks again.

 

[circledchicken]

one final thing (i promise)... is there a way i can lock the scroll bar to a specific range like say from A1:P70...this way the scroll bar wont go beyond that range??


thanks again.

Try experimenting with Freeze Panes - more information and examples here:

http://office.microsoft.com/en-us/excel-help/freeze-or-lock-rows-and-columns-HP010342542.aspx

 

[cantona_007]

ok thanks mate.