Help with a multi if then else formula.

[Pank]

I have the following scenario for which I would require one formula which
would be in cell E25:-

A numerical weight amount is input in cell A25.

If the weight is between greater than 1 and less than 41 KG, the user will
manually select a particular price, and therefore “Normal Price†to appear in
B25 and “1†to appear in B26. A formula should be inserted in B27 which is
B25*B26.

If the weight entered is greater than 40 and less then 101 KG, the user will
manually select an additional pricing and therefore “41 - 100 Price†to
appear in C25, “Normal Price†to appear in B25, B26 to contain “1†and the
result of the weight entered in A25 minus 40 to appear in C26. A formula
should be inserted in B27 which is B25*B26. Formula in C27 which is C25*C26.

If the weight enterer is greater than 101 KG, the user will manually select
an additional pricing and therefore “100+ Price†to appear in D25, “41 - 100
Price†to appear in C25, “Normal Price†to appear in B25. B26 should be set
to “1â€, C26 to “60†and D26 to A25-100. A formula should be inserted in B27
which is B25*B26. Formula in C27 which is C25*C26. Formula in D27 which is
D25*D26.

Once the appropriate values have been calculated in rows B26 to D26, the
User will enter numerics in B25 to D25. Lastly a formula in E27 to add
together B27+C27+D27

Examples:-

Weight > 1 and < 41 example:-

A25 = 40

B25 = Normal Price
B26 = 1

Weight > 40 and < 101 example:-

A25 = 99

C25 = 41 - 100 Price
B25 = Normal Price

C26 = 59
B26 = 1

Weight > 101 example:-

A25 = 215

D25 = 100+ Price
C25 = 41 - 100 Price
B25 = Normal Price

D26 = 115
C26 = 60
B26 = 1

Any assistance offered would be appreciated.

 

[Max]

One thought to help you along ...

Think you could try something like this to grab the labels (vlookup)
Eg in B25:
=IF(A25="","",VLOOKUP(A25,{1,"Normal Price";41,"41-100 Price";101,"100+
Price"},2))

p/s: To make your post attractive to answer, stick to 1 question per post

 

[Pank]

Max,

Firstly, many thanks for taking the time to answer the question.

Unfortunately, your solution does not answer my original question and my
Excel expertise is novice.

I may seem that I asked multiple questions, but I am sure that the above can
be achieved by the use of nested If Then Else statements.

I will endeavourer to plod on using manual procedures.

Once again thank you.

 

[Max]

Pank,

I re-looked at your numerical example
Try this set-up, it seems to return what you seek

In B25: =IF(A25<1,"","Normal Price")
In B26: =IF(B25="","",1)

In C25: =IF(A25<41,"","41-100 Price")
In C26: =IF(A25<41,"",IF(A25>100,60,A25-40))

In D25: =IF(A25<101,"","100+ Price")
In D26: =IF(A25<101,"",A25-100)

If the above was helpful, take a moment to press the "Yes" button below to
the question: "Was this post helpful to you?" from where you're reading this.
It'll ensure a longer shelf life to this thread for the general benefit of
other readers.

 

[Pank]

Max,

Once again thank you for your time in assisting me.

What you have provided is perfect.

Regards

 

[Max]

Welcome, Pank
Pl press the "Yes" button below (you forgot earlier)