Need Help fixing a formula

mj1000

New Member
Joined
Jan 20, 2005
Messages
24
I had this and because the if only allows 7 nested statements:
=IF(AND(C4<=4,C5="fr",C6="n"),'Rate Sheet'!C4,IF(AND(C4<=7,C5="fr",C6="n"),'Rate Sheet'!D4,IF(AND(C4<=10,C5="fr",C6="n"),'Rate Sheet'!E4,IF(AND(C4<=4,C5="fr",C6="y"),'Rate Sheet'!C5,IF(AND(C4<=7,C5="fr",C6="y"),'Rate Sheet'!D5,IF(AND(C4<=10,C5="fr",C6="y"),'Rate Sheet'!E5,IF(AND(C4<=4,C5="nc",C6="n"),'Rate Sheet'!C7,IF(AND(C4<=7,C5="nc",C6="n"),'Rate Sheet'!D7,IF(AND(C4<=10,C5="nc",C6="n"),'Rate Sheet'!E7,IF(AND(C4<=4,C5="nc",C6="y"),'Rate Sheet'!C8,IF(AND(C4<=7,C5="nc",C6="y"),'Rate Sheet'!D8,IF(AND(C4<=10,C5="nc",C6="y"),'Rate Sheet'!E8,))))))))))))

Seti helped be by giving me this (thanks again for that):
=OFFSET('Rate Sheet'!C3,4*(C5="nc")+(C6="y"),IF(C4<=4,0,IF(C4<=7,1,2)))

This statement returns 9 out of the possible 12 and I have been unsuccessful it getting it to return 12 out of 12. It is returning all but these three values:
IF(AND(C4<=4,C5="fr",C6="n"),'Rate Sheet'!C4,IF(AND(C4<=7,C5="fr",C6="n"),'Rate Sheet'!D4,IF(AND(C4<=10,C5="fr",C6="n"),'Rate Sheet'!E4,

Is anyone able to please help me fix the OFFSET statement?

Thanks tons :biggrin:
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
OK, this is fully tested and works on my setup. Hope it works for you too.

=OFFSET(C4,('Rate Sheet'!C5="nc")+3*('Rate Sheet'!C6="y"),IF('Rate Sheet'!C4<=4,0,IF('Rate Sheet'!C4<=7,1,2)))
 

mj1000

New Member
Joined
Jan 20, 2005
Messages
24
Not Working for Me

Thanks for your help Seti, but it’s not working for me, it is returning whatever number I enter in Cell C4.

What I would like to do is enter the following information a worksheet named ‘Premium’

Cell C4 will = 1-4, 5-7 or 8-10
Cell C5 will = fr or nc
Cell C6 will = y or n

If I enter any number between 1-4, fr and y
I would like it to return the value in the cell C4 located on the ‘Rate Sheet’

5-7, fr and y
return D4

8-10, fr and y
return E4

1-4, fr and n
return C5

5-7, fr and n
return D5

8-10, fr and n
return E5

1-4, nc and y
return C7

5-7, nc and y
return D7

8-10, nc and y
return E7

1-4, nc and n
return C8

5-7, nc and n
return D8

8-10, nc and n
return E8

Thanks again, I think I need to take and Excel for Dummies course.
 

mj1000

New Member
Joined
Jan 20, 2005
Messages
24
One more thing

The formula

=OFFSET('Rate Sheet'!C3,4*(C5="nc")+(C6="y"),IF(C4<=4,0,IF(C4<=7,1,2)))

worked except it would not return the values in cells C6, D6, E6.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Re: Not Working for Me

mj1000 said:
Thanks for your help Seti, but it’s not working for me, it is returning whatever number I enter in Cell C4.

What I would like to do is enter the following information a worksheet named ‘Premium’

Cell C4 will = 1-4, 5-7 or 8-10
Cell C5 will = fr or nc
Cell C6 will = y or n

If I enter any number between 1-4, fr and y
I would like it to return the value in the cell C4 located on the ‘Rate Sheet’

5-7, fr and y
return D4

8-10, fr and y
return E4

1-4, fr and n
return C5

5-7, fr and n
return D5

8-10, fr and n
return E5

1-4, nc and y
return C7

5-7, nc and y
return D7

8-10, nc and y
return E7

1-4, nc and n
return C8

5-7, nc and n
return D8

8-10, nc and n
return E8

Thanks again, I think I need to take and Excel for Dummies course.

Does this pick up the right rate?...

=INDEX('Rate Sheet'!$C$4:$E$8,VLOOKUP(C5&","&C6,{"fr,y",1;"fr,n",2;"nc,y",4;"nc,n",5},2,0),MATCH(C4,{1,5,8},1))
 

mj1000

New Member
Joined
Jan 20, 2005
Messages
24
YES!! THank you thank you!! I can even read the formula, thank you thank you!!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,115
Messages
5,599,809
Members
414,341
Latest member
Mohammedsobhey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top