Nested if statement problem

**ronnykc21** · 03-22-2019, 07:49 AM

I am trying to calculate a formula using nested if statement however, excel doesn't allow me to enter multiple if statement as it exceeds the limitation. is there anyone who can help me in creating a formula.

if Redundancy i.e B15 is Low and ARR value i.e B14 is according to the data in A4:A11 then the value øgb in B17 should be from the list C4:C11 and if Redundancy i.e B15 is Moderate and ARR value i.e B14 is according to the data in A4:A11 then the value øgb in B17 should be from the list D4:D11.

it would be much appreciated if anyone can help me out. thanks in advance.pile design calculation.jpg

**Sam Capricci** · 03-22-2019, 08:07 AM

based on your limited screenshot this looks like it would work for you...
=IF(B15="Low",LOOKUP(B14,{0,1.5,2,2.5,3,3.5,4,4.5},{0.67,0.61,0.56,0.52,0.48,0.45,0.42,0.4}),LOOKUP(B14,{0,1.5,2,2.5,3,3.5,4,4.5},{0.76,0.7,0.64,0.6,0.56,0.53,0.5,0.47}))
it has hand coded the values in both columns. If you have additional possibilities it only addresses low and not low.

**ronnykc21** · 03-22-2019, 08:10 AM

thanks Sambo kid, B15 will be a dropdown list displaying Low and Moderate. is it possible to adjust B17 according to dropdown list?

**ronnykc21** · 03-22-2019, 08:16 AM

Hi Sambo kid, formula did work out but it didnt mention equal in the formula according to the result.when i put B14 as 1.5, it was supposed to display 0.67 instead of 0.61.

**Sam Capricci** · 03-22-2019, 08:17 AM

not sure I am following you. The formula is for B17, if B15 changes based on a dropdown selection whatever appears in B15 and B14 will adjust in the formula. So if B14 has 1.6 and B15 shows as moderate B17 will show 0.70, if B15 has low and B14 has 1.6 the formula will return 0.61.
A dropdown selection for B15 should make no difference. Hope that helps.

**ronnykc21** · 03-22-2019, 08:25 AM

Agree. the formula works perfectly except if i input B14 value 1.5,2,2.5 the result was supposed to be 0.67,0.61, 0.56 instead of 0.61,0.56 and 0.52.the formula is showing greater than or equal to instead of less than or equal. thank you for your support

**ronnykc21** · 03-22-2019, 08:30 AM

thank you Sambo kid, i changed the values to 1.51 instead of 1.5. it worked. I appreciated your help.

**Sam Capricci** · 03-22-2019, 08:32 AM

to what level do the numbers in B14 go to (how many decimal places)?
if they are limited to one decimal place then change it to this...
=IF(B15="low",LOOKUP(B14,{0,1.51,2.01,2.51,3.01,3.51,4.01,4.51},{0.67,0.61,0.56,0.52,0.48,0.45,0.42,0.4}),LOOKUP(B14,{0,1.51,2.01,2.51,3.01,3.51,4.01,4.51},{0.76,0.7,0.64,0.6,0.56,0.53,0.5,0.47}))

**ronnykc21** · 03-22-2019, 08:34 AM

it should be upto 2 decimal.i have changed it as you have mentioned. thank you so much for this support.

**Sam Capricci** · 03-22-2019, 08:37 AM

you're welcome, AND thank you for the rep! Glad I could help.

**ronnykc21** · 03-22-2019, 08:38 AM

sure I did. thanks

**Bo_Ry** · 03-22-2019, 08:56 AM

Another one
Please try at B17
=LOOKUP(B14,{-1,1.5,2,2.5,3,3.5,4,4.5}+10^-9,INDEX(C4:D11,,2-(B15="low")))

**ronnykc21** · 03-22-2019, 09:00 AM

Thank you Bo_Ry. it works perfectly fine.

Nested if statement problem

LinkBack

Thread Tools

Rate This Thread

Display

Nested if statement problem

Re: Nested if statement problem

Re: Nested if statement problem

Re: Nested if statement problem

Re: Nested if statement problem

Re: Nested if statement problem

Re: Nested if statement problem

Re: Nested if statement problem

Re: Nested if statement problem

Re: Nested if statement problem

Re: Nested if statement problem

Re: Nested if statement problem

Re: Nested if statement problem

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Problem with Nested OR Statement

Problem with Nested If statement

Nested IF statement problem with < and >

Nested IF statement problem

Problem with nested if statement

Nested IF Statement problem

Nested if statement problem!

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions