I am using an IF formula to calculate between two cells, one is J (width) the
other is K (length). Currently these formulas give an answer that then has
to be rounded up based on the decimal place. I need the formula to also
round up the amount to quarter increments. For example if the answer is 1.17
then the formula needs to make it 1.25, if it is 1.33 then the formula needs
to make it 1.5, and finally if it is 1.63 then the formula needs to make it
1.75. So how do I add or make the formula round up to quarter increments?
The formula that I am using is:
=IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2)))

Tty:

=IF(J41<=3,CEILING(K41/4,0.25),IF(J41<=4,CEILING(K41/3,0.25),IF(J41>4,CEILING(K41/2,0.25))))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"JM_Mc" <JMMc@discussions.microsoft.com> wrote in message
news:F29EAA29-B8A7-4841-9C96-1A05A76F1670@microsoft.com...
>I am using an IF formula to calculate between two cells, one is J (width)
>the
> other is K (length). Currently these formulas give an answer that then
> has
> to be rounded up based on the decimal place. I need the formula to also
> round up the amount to quarter increments. For example if the answer is
> 1.17
> then the formula needs to make it 1.25, if it is 1.33 then the formula
> needs
> to make it 1.5, and finally if it is 1.63 then the formula needs to make
> it
> 1.75. So how do I add or make the formula round up to quarter increments?
> The formula that I am using is:
> =IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2)))
>

Thank you so much this has solved a question that has been around for quite
sometime. Could you answer another formula for me. I have two cells that I
need to sum up, one cell has a set value (E), and the second cell is a value
that is only entered when needed (F). How do I sum up the two only when F
has a value in it? (If there is no value in (F) I want the formula to not
place (E)'s value as the sum).



"Sandy Mann" wrote:

> Tty:
>
> =IF(J41<=3,CEILING(K41/4,0.25),IF(J41<=4,CEILING(K41/3,0.25),IF(J41>4,CEILING(K41/2,0.25))))
>
> --
> HTH
>
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
>
> sandymann2@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "JM_Mc" <JMMc@discussions.microsoft.com> wrote in message
> news:F29EAA29-B8A7-4841-9C96-1A05A76F1670@microsoft.com...
> >I am using an IF formula to calculate between two cells, one is J (width)
> >the
> > other is K (length). Currently these formulas give an answer that then
> > has
> > to be rounded up based on the decimal place. I need the formula to also
> > round up the amount to quarter increments. For example if the answer is
> > 1.17
> > then the formula needs to make it 1.25, if it is 1.33 then the formula
> > needs
> > to make it 1.5, and finally if it is 1.63 then the formula needs to make
> > it
> > 1.75. So how do I add or make the formula round up to quarter increments?
> > The formula that I am using is:
> > =IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2)))
> >
>
>
>

Try:

=IF(F41="","",SUM(E41:F41))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"JM_Mc" <JMMc@discussions.microsoft.com> wrote in message
news:458EF6A8-51C2-4839-BCB4-1E00D7F672FC@microsoft.com...
> Thank you so much this has solved a question that has been around for
> quite
> sometime. Could you answer another formula for me. I have two cells that
> I
> need to sum up, one cell has a set value (E), and the second cell is a
> value
> that is only entered when needed (F). How do I sum up the two only when F
> has a value in it? (If there is no value in (F) I want the formula to not
> place (E)'s value as the sum).
>
>
>
> "Sandy Mann" wrote:
>
>> Tty:
>>
>> =IF(J41<=3,CEILING(K41/4,0.25),IF(J41<=4,CEILING(K41/3,0.25),IF(J41>4,CEILING(K41/2,0.25))))
>>
>> --
>> HTH
>>
>> Sandy
>> In Perth, the ancient capital of Scotland
>> and the crowning place of kings
>>
>> sandymann2@mailinator.com
>> Replace @mailinator.com with @tiscali.co.uk
>>
>>
>> "JM_Mc" <JMMc@discussions.microsoft.com> wrote in message
>> news:F29EAA29-B8A7-4841-9C96-1A05A76F1670@microsoft.com...
>> >I am using an IF formula to calculate between two cells, one is J
>> >(width)
>> >the
>> > other is K (length). Currently these formulas give an answer that then
>> > has
>> > to be rounded up based on the decimal place. I need the formula to
>> > also
>> > round up the amount to quarter increments. For example if the answer
>> > is
>> > 1.17
>> > then the formula needs to make it 1.25, if it is 1.33 then the formula
>> > needs
>> > to make it 1.5, and finally if it is 1.63 then the formula needs to
>> > make
>> > it
>> > 1.75. So how do I add or make the formula round up to quarter
>> > increments?
>> > The formula that I am using is:
>> > =IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2)))
>> >
>>
>>
>>
>

Here's another option for your first question.....

=CEILING(K41/INT(7-J41),0.25)

"Sandy Mann" wrote:

> Try:
>
> =IF(F41="","",SUM(E41:F41))
>
> --
> HTH
>
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
>
> sandymann2@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "JM_Mc" <JMMc@discussions.microsoft.com> wrote in message
> news:458EF6A8-51C2-4839-BCB4-1E00D7F672FC@microsoft.com...
> > Thank you so much this has solved a question that has been around for
> > quite
> > sometime. Could you answer another formula for me. I have two cells that
> > I
> > need to sum up, one cell has a set value (E), and the second cell is a
> > value
> > that is only entered when needed (F). How do I sum up the two only when F
> > has a value in it? (If there is no value in (F) I want the formula to not
> > place (E)'s value as the sum).
> >
> >
> >
> > "Sandy Mann" wrote:
> >
> >> Tty:
> >>
> >> =IF(J41<=3,CEILING(K41/4,0.25),IF(J41<=4,CEILING(K41/3,0.25),IF(J41>4,CEILING(K41/2,0.25))))
> >>
> >> --
> >> HTH
> >>
> >> Sandy
> >> In Perth, the ancient capital of Scotland
> >> and the crowning place of kings
> >>
> >> sandymann2@mailinator.com
> >> Replace @mailinator.com with @tiscali.co.uk
> >>
> >>
> >> "JM_Mc" <JMMc@discussions.microsoft.com> wrote in message
> >> news:F29EAA29-B8A7-4841-9C96-1A05A76F1670@microsoft.com...
> >> >I am using an IF formula to calculate between two cells, one is J
> >> >(width)
> >> >the
> >> > other is K (length). Currently these formulas give an answer that then
> >> > has
> >> > to be rounded up based on the decimal place. I need the formula to
> >> > also
> >> > round up the amount to quarter increments. For example if the answer
> >> > is
> >> > 1.17
> >> > then the formula needs to make it 1.25, if it is 1.33 then the formula
> >> > needs
> >> > to make it 1.5, and finally if it is 1.63 then the formula needs to
> >> > make
> >> > it
> >> > 1.75. So how do I add or make the formula round up to quarter
> >> > increments?
> >> > The formula that I am using is:
> >> > =IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2)))
> >> >
> >>
> >>
> >>
> >
>
>
>

Sorry, that would need to be....

=CEILING(K41/MIN(4,MAX(2,INT(7-J41))),0.25)



"daddylonglegs" wrote:

> Here's another option for your first question.....
>
> =CEILING(K41/INT(7-J41),0.25)
>
> "Sandy Mann" wrote:
>
> > Try:
> >
> > =IF(F41="","",SUM(E41:F41))
> >
> > --
> > HTH
> >
> > Sandy
> > In Perth, the ancient capital of Scotland
> > and the crowning place of kings
> >
> > sandymann2@mailinator.com
> > Replace @mailinator.com with @tiscali.co.uk
> >
> >
> > "JM_Mc" <JMMc@discussions.microsoft.com> wrote in message
> > news:458EF6A8-51C2-4839-BCB4-1E00D7F672FC@microsoft.com...
> > > Thank you so much this has solved a question that has been around for
> > > quite
> > > sometime. Could you answer another formula for me. I have two cells that
> > > I
> > > need to sum up, one cell has a set value (E), and the second cell is a
> > > value
> > > that is only entered when needed (F). How do I sum up the two only when F
> > > has a value in it? (If there is no value in (F) I want the formula to not
> > > place (E)'s value as the sum).
> > >
> > >
> > >
> > > "Sandy Mann" wrote:
> > >
> > >> Tty:
> > >>
> > >> =IF(J41<=3,CEILING(K41/4,0.25),IF(J41<=4,CEILING(K41/3,0.25),IF(J41>4,CEILING(K41/2,0.25))))
> > >>
> > >> --
> > >> HTH
> > >>
> > >> Sandy
> > >> In Perth, the ancient capital of Scotland
> > >> and the crowning place of kings
> > >>
> > >> sandymann2@mailinator.com
> > >> Replace @mailinator.com with @tiscali.co.uk
> > >>
> > >>
> > >> "JM_Mc" <JMMc@discussions.microsoft.com> wrote in message
> > >> news:F29EAA29-B8A7-4841-9C96-1A05A76F1670@microsoft.com...
> > >> >I am using an IF formula to calculate between two cells, one is J
> > >> >(width)
> > >> >the
> > >> > other is K (length). Currently these formulas give an answer that then
> > >> > has
> > >> > to be rounded up based on the decimal place. I need the formula to
> > >> > also
> > >> > round up the amount to quarter increments. For example if the answer
> > >> > is
> > >> > 1.17
> > >> > then the formula needs to make it 1.25, if it is 1.33 then the formula
> > >> > needs
> > >> > to make it 1.5, and finally if it is 1.63 then the formula needs to
> > >> > make
> > >> > it
> > >> > 1.75. So how do I add or make the formula round up to quarter
> > >> > increments?
> > >> > The formula that I am using is:
> > >> > =IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2)))
> > >> >
> > >>
> > >>
> > >>
> > >
> >
> >
> >

Hi Sandy,

You might want to simplify the formula below to read:

=CEILING(K1/IF(J1<=3,4,IF(J1<=4,3,IF(J1>4,2))),0.25)

Cheers,
Shane Devenshire
Microsoft Excel MVP

"Sandy Mann" <sandymann2@mailinator.com> wrote in message
news:eivwzHQvIHA.4772@TK2MSFTNGP03.phx.gbl...
> Tty:
>
> =IF(J41<=3,CEILING(K41/4,0.25),IF(J41<=4,CEILING(K41/3,0.25),IF(J41>4,CEILING(K41/2,0.25))))
>
> --
> HTH
>
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
>
> sandymann2@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "JM_Mc" <JMMc@discussions.microsoft.com> wrote in message
> news:F29EAA29-B8A7-4841-9C96-1A05A76F1670@microsoft.com...
>>I am using an IF formula to calculate between two cells, one is J (width)
>>the
>> other is K (length). Currently these formulas give an answer that then
>> has
>> to be rounded up based on the decimal place. I need the formula to also
>> round up the amount to quarter increments. For example if the answer is
>> 1.17
>> then the formula needs to make it 1.25, if it is 1.33 then the formula
>> needs
>> to make it 1.5, and finally if it is 1.63 then the formula needs to make
>> it
>> 1.75. So how do I add or make the formula round up to quarter
>> increments?
>> The formula that I am using is:
>> =IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2)))
>>
>
>

Hi again,

I see you got a shorter formula already, that one can also be shortened to:

=CEILING(K1/MIN(4,MAX(2,7-J1)),0.25)

Cheers,
Shane Devenshire
Microsoft Excel MVP

"JM_Mc" <JMMc@discussions.microsoft.com> wrote in message
news:F29EAA29-B8A7-4841-9C96-1A05A76F1670@microsoft.com...
> I am using an IF formula to calculate between two cells, one is J (width)
> the
> other is K (length). Currently these formulas give an answer that then
> has
> to be rounded up based on the decimal place. I need the formula to also
> round up the amount to quarter increments. For example if the answer is
> 1.17
> then the formula needs to make it 1.25, if it is 1.33 then the formula
> needs
> to make it 1.5, and finally if it is 1.63 then the formula needs to make
> it
> 1.75. So how do I add or make the formula round up to quarter increments?
> The formula that I am using is:
> =IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2)))