how to insert function?
 

if cell h4=16, subtract 15, if cell h4=31 subtract 30,if cell h4=46
subtract 45
does any one know if all this can be included in one formula?
Many thanks in anticipation of your reply


--
tmela
------------------------------------------------------------------------
tmela's Profile: http://www.excelforum.com/member.php...o&userid=37745
View this thread: http://www.excelforum.com/showthread...hreadid=573580

how to insert function?
 

It looks like you want the remainder after dividing by 15.

If that's true:

=mod(H4,15)





tmela wrote:

if cell h4=16, subtract 15, if cell h4=31 subtract 30,if cell h4=46
subtract 45
does any one know if all this can be included in one formula?
Many thanks in anticipation of your reply

--
tmela
------------------------------------------------------------------------
tmela's Profile: http://www.excelforum.com/member.php...o&userid=37745
View this thread: http://www.excelforum.com/showthread...hreadid=573580

--

Dave Peterson

how to insert function?
 

You're probably right Dave but if the OP wants 15 for multiples of 15 then I
would suggest:

=H4-(CEILING(H4,15)/15-1)*15

--

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Dave Peterson" wrote in message
...
It looks like you want the remainder after dividing by 15.

If that's true:

=mod(H4,15)





tmela wrote:

if cell h4=16, subtract 15, if cell h4=31 subtract 30,if cell h4=46
subtract 45
does any one know if all this can be included in one formula?
Many thanks in anticipation of your reply

--
tmela
------------------------------------------------------------------------
tmela's Profile:
http://www.excelforum.com/member.php...o&userid=37745
View this thread:
http://www.excelforum.com/showthread...hreadid=573580

--

Dave Peterson

how to insert function?
 

=IF(H4=46,H4-45,IF(H4=31,H4-30,IF(H4=16,H4-15,H4))) ' pasted into A1 the
result in A1 is what you wanted tmela.

"tmela" wrote:


if cell h4=16, subtract 15, if cell h4=31 subtract 30,if cell h4=46
subtract 45
does any one know if all this can be included in one formula?
Many thanks in anticipation of your reply


--
tmela
------------------------------------------------------------------------
tmela's Profile: http://www.excelforum.com/member.php...o&userid=37745
View this thread: http://www.excelforum.com/showthread...hreadid=573580

how to insert function?
 

This type if statements must be run backwards. The lowest statement is met
every time the highest statement is met, therefore you must test from highest
to lowest.

"Rookie 1st class" wrote:

=IF(H4=46,H4-45,IF(H4=31,H4-30,IF(H4=16,H4-15,H4))) ' pasted into A1 the
result in A1 is what you wanted tmela.

"tmela" wrote:


if cell h4=16, subtract 15, if cell h4=31 subtract 30,if cell h4=46
subtract 45
does any one know if all this can be included in one formula?
Many thanks in anticipation of your reply


--
tmela
------------------------------------------------------------------------
tmela's Profile: http://www.excelforum.com/member.php...o&userid=37745
View this thread: http://www.excelforum.com/showthread...hreadid=573580