Excel Problem. Prease provide a function that will work in Excel . Problem #10:
ID: 3700703 • Letter: E
Question
Excel Problem. Prease provide a function that will work in Excel.
Problem #10: Embedded if Statements Use a set of embedded if statements to create a single formula that will calculate the Danger level of the following water samples. Samples taken before September 1, 2008 samples above 300 ppm have a "High" dangel level, samples between 200 and 300 have a "Medium" level, and samples below 200 have a "low" level. Samples taken after September 1, 2008 samples above 400 ppm have a "High" dangel level, samples between Danger Level PPM 130 250 180 305 489 328 293 395 79 390 Date 10/5/2008 7/13/2008 9/21/2008 3/6/2008 4/12/2008 12/8/2008 5/18/2008 11/24/2008 6/24/2008 4/21/2008Explanation / Answer
Referring to above question
We need to use a macro level programming language called visual basic(VBA)
So as per the above question here we need to use IF statement to derive the excel formula to find out given functions;
NOTE: Please note that some of the questions part is missing like condition after 1 Septmber 2008 only danger level high condition is given not for medium and for low So request you to come back with complete question,Although am solving this one as much condition is given.
Basic If function is
First we need to put any test case like condition we are testing see in example we are testing A2 is greater than 80 then true condition (i.e pass as per above example" will return else fail
So in nested IF condition will use this concept
below id the derived excel fromula to find out these conditions respectively as per the data given above
In outer IF we are first checking if date is before 1 Sept 2008 then moving to next True statement i.e another IF(IF(B10>300,"High") So this case outer IF will run first then inner IF
=IF(A1<"9/1/2008",IF(B1>300,"High",IF(B1>200,"Medium",IF(B1<200,"Low")))) =IF(A2<"9/1/2008",IF(B2>300,"High",IF(B2>200,"Medium",IF(B2<200,"Low")))) =IF(A3<"9/1/2008",IF(B3>300,"High",IF(B3>200,"Medium",IF(B3<200,"Low")))) =IF(A4<"9/1/2008",IF(B4>300,"High",IF(B4>200,"Medium",IF(B4<200,"Low")))) =IF(A5<"9/1/2008",IF(B5>300,"High",IF(B5>200,"Medium",IF(B5<200,"Low")))) =IF(A6<"9/1/2008",IF(B6>300,"High",IF(B6>200,"Medium",IF(B6<200,"Low")))) =IF(A7<"9/1/2008",IF(B7>300,"High",IF(B7>200,"Medium",IF(B7<200,"Low")))) =IF(A8<"9/1/2008",IF(B8>300,"High",IF(B8>200,"Medium",IF(B8<200,"Low")))) =IF(A9<"9/1/2008",IF(B9>300,"High",IF(B9>200,"Medium",IF(B9<200,"Low")))) =IF(A10<"9/1/2008",IF(B10>300,"High",IF(B10>200,"Medium",IF(B10<200,"Low"))))Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.