Problem with 64 levels of nesting

marita

New Member
Joined
Aug 8, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello! I have two columns in the first sheet, one for the month and the other for the time (hourly time step). I also have a second sheet where water demand depends on the month and the hour, that means other demand for January at 1:00, other for January at 5:00, other for July at 17:00 etc. (12x24 table). So i want for each time step to for a whole year to check the month and the hour and to take the correct amount of water from the second sheet.
I used if-and (code example: =IF(AND(B8=1,E8=0), Data!G26,IF(AND(B8=1,E8=1), Data!G27,IF(AND(B8=1,E8=2), Data!G28,IF(AND(B8=1,E8=3)... etc.) where B is the month, E the time and Data the second sheet but if i do this for all the months (that means 288 if) i get the error "This formula can't be entered because it uses more than 64 levels of nesting"
Any ideas how can i overcome the problem and get my results, using maybe another excel function?

Thank you in advance!
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,670
Office Version
  1. 2010
Platform
  1. Windows
The way I would do that is with a "helper" column, in the helper column I would create a number which is 24 times the month number plus the hour. This will give you an unique number for each combination of month and hour. Then in your second sheet you can index/Match to pick the correct value, by multiplying the month by 24 and adding the hour
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,026
Office Version
  1. 365
Platform
  1. Windows
From your example, it looks like B8 refers to the column of the table and E8 refers to the row, if that is the case then something like

=INDEX(Data!$G$26:$$R$49,E8+1,B8)

Should do the trick (note that this involves guesswork on the full layout, it will most likely need some adjustments).
 

marita

New Member
Joined
Aug 8, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
wow! jasonb75 that easy? thank you very much. no adjustments needed! it worked!
offthelip thank you too for your time to answer my question!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,530
Messages
5,602,196
Members
414,513
Latest member
junbuggle

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