Forum Discussion
Stay calculator
Hi
I am trying to make a calculator that works out the number of stay over nights required for an engineer.
In the below screen shot of the spread sheet I have a 2 blue squares for data entry and the answer needs to display in the yellow square.
The number in B4 will relate to the hours column "G" in the table and the number in A14 will determine the Zone number as shown in F14.
For the calculation in the yellow square, I need the formula to select the Zone column as reflected in Cell B5 the hours column and return the answer shown in the corresponding cell. For example if the customer is in Zone 4 and the engineer needs to do 12 hours of work the answer should be 4 stays as reflected in cell K13.
I was thinking of an If this then use that type of calculation but I cannot find anything that will do what I am looking for, so any help would be greatly appreciated.
For B6 cell you may use-
=INDEX(H2:L1000,MATCH(B4,G2:G1000,0),MATCH(B5,H1:L1,0))
4 Replies
For B6 cell you may use-
=INDEX(H2:L1000,MATCH(B4,G2:G1000,0),MATCH(B5,H1:L1,0))There are many ways to tackle this. Depending on how general or specific a solution and personal preference.
for example the formula you are showing to get the "Zone" you could directly use to get the column from the table something like:
=INDEX(H2:L29, B4, MATCH(1, (B14:B18<=A14)*(C14:C18>A14),0))Hi m_tarler
Thank you for the help unfortunately the formula that you supplied just reports and error saying I have specified too many arguments.
sorry about that not sure how that extra term got in there. I believe I corrected it above.
basically it is just using INDEX for the row based on B4 and the column based on the Match function you already were using as part of the formula in cell F14.
