Custom rounding function

**doctordowling** · 08-30-2010, 12:53 PM

Hi all,

I'm new to the forums and new to excel functions and programming. I'm trying to create a custom ROUND function that will allow me to do the following:

I'm working on a spreadsheet for a university game room that tracks times and then applies a dollar amount based on time played.

For example:

If someone plays a game for an hour from 1:00 pm to 2:00 pm they will be charged 1 for that hour. (1 per/hour)

What I want to do is allow a grace period so that if someone played an extra fifteen minutes they would still only get charged the rate for one hour of play.

So, if someone played from 1:00 - 2:15 they would be charged 1 dollar but if they played from 1:00 -2:16 (or longer) they would be charged 2 dollars.

Is there a way to create a custom formula that could allow this?

**daddylonglegs** · 08-30-2010, 01:18 PM

If time played is in A1 try

=CEILING(MAX(0,(A1*24)-0.25),1)

**doctordowling** · 08-30-2010, 02:42 PM

Originally Posted by daddylonglegs

If time played is in A1 try

=CEILING(MAX(0,(A1*24)-0.25),1)

Thank you! That worked perfectly!

I actually have another question though!

Im using the IF function to populate a field like so:

IF(B4="Student",1,2). Where Column B will either say "student" or "non student"

The only issue is that since I'm using a large form all the blank fields for colum B that have not been populated, default to the FALSE which is 2. Is there any way to copy the formula to cells without it auto populating the FALSE? That is, I want it to stay blank until columb B is either populated with "student" or "non student"

I've tried incorporating the AND function but havnt had any luck thus far.