if formula

N

Nigel123

Hi im wanting to set up 4 formulas in 1 cell

I have got the first part done which is

=IF(D2=0,"0",IF(D2>0.01<15,"7.25",IF(D2<=15,7.25,IF(D2>15,(D2-15)*0.29+7.25))))

However the next step is to use this formula if cell e2 matches nd16 and if
it matches nd17 it will automatically use a different formula and if it
matches nd18 it will automatically use another formula and so on so fourth
can someone help me??

cheers
 
M

Mike H

Hi,

The formula you posted didn't work and here's a corrected one

=IF(D2=0,0,IF(AND(D2>0.01,D2<=15),7.25,IF(D2>15,(D2-15)*0.29+7.25)))

I'm afraid I don't understand the second part of your question.

Mike
 
N

Nigel123

Hi

is cell e2 says nd16 i want it to use this formula which is correct on cell f2

=IF(D2=0,"0",IF(D2>0.01<15,"7.25",IF(D2<=15,7.25,IF(D2>15,(D2-15)*0.29+7.25))))

and if cell e2 says cp16 I want cell f2 to use this formula

=IF(D2=0,"0",IF(D2<=5,"6"))

and there are a range of other formulas I want it to use if cell e2 where to
say nd12, nd16, ns12 etc

hope this helps
 
S

Shane Devenshire

Hi,

The basic idea:

=IF(E2="nd16,IF(D2=0,"0",IF(D2>0.01<15,"7.25",IF(D2<=15,7.25,IF(D2>15,(D2-15)*0.29+7.25)))),"my other formula here")
 
D

David Biddulph

I'm surprised if you get FALSE from that. To me (and to Excel), it looks an
invalid formula.

Changing it to a valid formula, and putting in some corrections which you
may want (assuming for example that you wanted 7.25 to be a number and not a
text string), it would be:
=IF(E2="nd16",IF(D2=0,0,IF(AND(D2>0.01,D2<15),7.25,IF(D2<=15,7.25,(D2-15)*0.29+7.25))),"myother formula here")You can then see that the part which you had as D2>0.01<15 is unnecessary,because you cover that range anyway in the D2<=15 condition, hence furthersimplification gives:=IF(E2="nd16",IF(D2=0,0,IF(D2<=15,7.25,(D2-15)*0.29+7.25)),"my other formulahere")--David Biddulph"Nigel123" <[email protected]> wrote in messageHi>> I have tried this formula and it says false please help??>> "Shane Devenshire" wrote:>>> Hi,>>>> The basic idea:>>>>=IF(E2="nd16,IF(D2=0,"0",IF(D2>0.01<15,"7.25",IF(D2<=15,7.25,IF(D2>15,(D2-15)*0.29+7.25)))),"my other formula here")>>>> -->> If this helps, please click the Yes button.>>>> Cheers,>> Shane Devenshire>>>>>> "Nigel123" wrote:>>>> > Hi>> >>> > is cell e2 says nd16 i want it to use this formula which is correct oncell f2>> >>> >=IF(D2=0,"0",IF(D2>0.01<15,"7.25",IF(D2<=15,7.25,IF(D2>15,(D2-15)*0.29+7.25))))>> >>> > and if cell e2 says cp16 I want cell f2 to use this formula>> >>> > =IF(D2=0,"0",IF(D2<=5,"6"))>> >>> > and there are a range of other formulas I want it to use if cell e2where to>> > say nd12, nd16, ns12 etc>> >>> > hope this helps>> >>> >>> >>> >>> > "Mike H" wrote:>> >>> > > Hi,>> > >>> > > The formula you posted didn't work and here's a corrected one>> > >>> > > =IF(D2=0,0,IF(AND(D2>0.01,D2<=15),7.25,IF(D2>15,(D2-15)*0.29+7.25)))>> > >>> > > I'm afraid I don't understand the second part of your question.>> > >>> > > Mike>> > >>> > > "Nigel123" wrote:>> > >>> > > > Hi im wanting to set up 4 formulas in 1 cell>> > > >>> > > > I have got the first part done which is>> > > >>> > > >=IF(D2=0,"0",IF(D2>0.01<15,"7.25",IF(D2<=15,7.25,IF(D2>15,(D2-15)*0.29+7.25))))>> > > >>> > > > However the next step is to use this formula if cell e2 matchesnd16 and if>> > > > it matches nd17 it will automatically use a different formula andif it>> > > > matches nd18 it will automatically use another formula and so on sofourth>> > > > can someone help me??>> > > >>> > > > cheers
 
N

Nigel123

Hi this doesn't help however this is what I want it to do

Weight Type of Service Cost
(Culumn D) (Culumn E) (Column F)

Column D will be manually entered.

Column E will be one of the following:-
CP16, ND12, ND16, NS12, RD1 and RD2

Column F I want to have the formula which is what I'm trying to work out. I
want it to automatically know which formular to use depending what Column E's
cell says. I have wrote down what each code means.

CP16 = 6 with a maximum of 5kg

ND 12 = 13 for the first 10kg then .29 per 1kg for the next 100kg and then
..42 per 1kg for the next 999kg

ND16 = 7.25 for the first 15kg then 0.29 per 1kg for the next 100kg and then
..42 per 1kg for the next 999kg

NS12 = 33 for the first 10kg and then 0.29 per 1kg for the next 100kg and
then .42 per 1kg for the next 999kg

RD1 = 14.70 for then first 10kg then 0.29 per 1kg for the next 100kg and
then .42 per 1kg for the next 999kg

RD2 = 15.75 for the first 10kg and then 0.29 per 1kg for the next 100kg and
then .42 per 1kg for the next 999kg

I hope this helps you understand a little more

Please can you help me as I am really struggling with this email.
 
Top