Help - looking for advise with IF and AND formula (German wenn / und)?

MSchädler

Board Regular
Joined
Apr 27, 2017
Messages
90
Hi MrExcel,
I would like to find a easier way using my excel formula and maybe someone can give me some advice.
The formula I have set up and is working is as follow (in german):
=WENN(UND(A3<=KIAO!$G$10;KIAO!B3=KIAO!$E$10);KIAO!$H$10;WENN(UND(A3<=KIAO!$G$11;KIAO!B3=KIAO!$E$10);KIAO!$H$11;WENN(UND(A3>=KIAO!$G$12;KIAO!B3=KIAO!$E$10);KIAO!$H$12;WENN(UND(A3<=KIAO!$G$13;B3=KIAO!$E$13);KIAO!$H$13;WENN(UND(A3>KIAO!$G$13;A3<=KIAO!$G$14;B3=KIAO!$E$13);KIAO!$H$14;WENN(UND(A3>KIAO!$G$14;B3=KIAO!$E$13);KIAO!$H$15;WENN(KIAO!$G$14;KIAO!$H$15;FALSCH)))))))

My sheet is setup as follow (see image) and I have adjusted the excel sheet so that I can place the question.
Description:
- In column A we have length sizes and this is entered manually.
- In column B we can choose (drop down) between standard or reduced (from E10 to E15).
- Column C should give the result (price) according to the different parameters of the table, Column E10 to H15.
To make my formula work I had to introduce Column G with fixed numbers.
The 3 categories in column F are: less than 25 (<25), between 25 and 40 (25-40), over 40 (>40).

Example of the result in a cell in column C;
if 15metre is entered (column A) and a standard case is picked (column B) the formula has to figure, the entered metre is in what of the three parameters (<25; 25-40; >40). Then from column B we defined Standard or Reduced and then the formula should return the right price into column C.
The result in this case is: 2'100.00

Is there a easier formula for this task?
Thanks for any input.
 

Attachments

  • Excel IF.JPG
    Excel IF.JPG
    62.6 KB · Views: 3

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,318
Office Version
  1. 365
Platform
  1. Windows
Ja, there is a much easier method.

Copy downwards the formula in C3. I translated it into Deutsch: =INDEX($F$6:$G$8;VERGLEICH(A3;$E$6:$E$8);VERGLEICH(B3;$F$5:$G$5;0))

MrExcel posts18.xlsx
ABCDEFG
2length (m)S/RPrice
30.00standard2,100.00
40.00reduced1,380.00
50.01standard2,100.00length (m)StandardReduced
60.01reduced1,380.000.002,100.001,380.00
724.99standard2,100.0025.003,200.001,520.00
824.99reduced1,380.0040.003,400.001,470.00
925.00standard3,200.00
1025.00reduced1,520.00
1125.01standard3,200.00
1225.01reduced1,520.00
1340.00standard3,400.00
1440.00reduced1,470.00
1540.01standard3,400.00
1640.01standard3,400.00
1740.01reduced1,470.00
1840.01reduced1,470.00
1941.00standard3,400.00
2041.00reduced1,470.00
Sheet36
Cell Formulas
RangeFormula
C3:C20C3=INDEX($F$6:$G$8,MATCH(A3,$E$6:$E$8),MATCH(B3,$F$5:$G$5,0))
 

MSchädler

Board Regular
Joined
Apr 27, 2017
Messages
90
Hello DRSteele, great input.
I implemented your approach in my excel sheet and made it work.
Your way is also a lot easier if I have to change the numbers in my table,
Thanks a lot, it works just fine.
Regards
Marc
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,318
Office Version
  1. 365
Platform
  1. Windows
Super. You're welcome.

I was worried I might end up guilty of Verschlimmbesserung.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,987
Messages
5,575,386
Members
412,659
Latest member
oliverreyes
Top