grid formula

[bdf1965]

I need a formula to alter sizes on a grid which will then give me the appropriate cost from the grid , cells B1 through to E1 would be widths going up at 150mm at a time and cells A2 through to A5 would be the heights going up at 150mm a time where these cross there is an appropriate cost , by altering any size how can i get the cost to change please

[Pete_UK]

What do you mean by "altering the size"? Do you want to increase or decrease the 150mm increment in either the width or the height (or both) and get the prices to adjust in proportion, or do you want to specify a particular size in another pair of cells and get the price of that size returned to another cell?

It might help if you posted an example workbook. (see the FAQ to find out how)

Pete

[bdf1965]

hi pete
its the second option , its for conservatory roofs
i have the width in cell K6 & the depth in I6 , so when i change the sizes in these cells i need the prices taken off the appropriate cells in the cost grid

[Pete_UK]

I would still like to see a sample workbook, as 5 columns wide and 5 rows deep at 150mm each would not give you a very big roof.

Pete

[bdf1965]

i have attached an actual sample of price list , what i need is as the sizes in H1 & J1 change i need the price from the grid to be entered in a different cell.
the grid is in 250mm stages , so once over half way the next highest price is charged ie the roof price at the sizes in H1 & J1 should give a price of 2610

[Pete_UK]

In the attached file I have put this formula in L1:

=INDEX($B$6:$K$13,MATCH(CEILING(J1-0.124,0.25)*1000,$A$6:$A$13,0),MATCH(CEILING(H1-0.124,0.25)*1000,$B$4:$K$4,0))

to give you the price based on the numbers in H1 and J1 (4.25 and 2.85 in the example). 2.85 is closer to 2.75 than 3.00, so the answer given is 2493, but the changeover occurs at the mid-point.

Hope this helps.

Pete

[bdf1965]

brilliant , thank you very much , just as an after thought is there a way of adding the grid sizes to the formula as a look up type thing please.

[Pete_UK]

I'm not really sure what you mean. Do you want the actual sizes used by the formula? If so, you could put this formula in H2:

=IF(H1="","",CEILING(H1-0.124,0.25))

and this one in J2:

=IF(J1="","",CEILING(J1-0.124,0.25))

If that is not what you mean, please give an example of what you need.

Hope this helps.

Pete

[bdf1965]

for example the base formula i used =if(j42="yes",(lookup(h42,{9,9.5,10},{"1564","1621","1704"})),if(j42="no","0")) this is a shortened version ,the 9 and 9.5 etc are the sizes and the 1654 etc are the costs , could the roof sizes and costs be inserted into a similar formula

[Pete_UK]

So, do you mean that you want to include the numbers from the grid within the formula and dispense with the grid? If so, I wouldn't advise it. The advantage of using a table of data is that if one or more values change then you only need to change them within the table - the formula remains the same. If you include the values within the formula then it could be quite difficult to make the necessary changes if the data changes.

But, if you want to go that route, you can set up 2-D arrays by separating the rows using a semi-colon, so an array for your costs would look like this:

{1674,1723,1795,1868,1948,2042,2114,2197,2267,2338;1778,1841,1925,1995,2065,2174,2305,2314,2398,2470;1901,1971,2042, .... }

and so on for the rest of the data. That would replace the $B$6:$K$13 in the formula I gave you. You would also need to replace the ranges $A$6:$A$13 and $B$4:$K$4 with 1-D arrays.

Hope this helps.

Pete

P.S. - If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[bdf1965]

hi pete i have run in to a further problem most of the roofs are ok except 2 , i have attached the work book
formula i have entered for this roof is =IF(H1="YES",(INDEX($B$5:$H$8,MATCH(CEILING(K6-0.001,0.25)*1000,$A$5:$A$8,0),MATCH(CEILING(I6-0.001,0.25)*1000,$B$3:$H$3,0))),IF(H1="NO","0")) but this returns N/A , could you tell me were im going wrong please
brian

[Pete_UK]

I can't see that formula in the attached workbook, but I do see that you have changed the increment for the width, and you have also changed where the sizes are entered.

If you are using H1 so that it can only contain Yes or No, then you don't need to check for the No condition after the Yes, so you can do something like this:

=IF(H1="Yes", size_calculation, 0)

where size_calculation is the formula that I gave you before (or modified to suit your latest changes).

Hope this helps.

Pete

[bdf1965]

pete
i have re sent the workbook with the formula in it , this returns #n/a , i have changed the original 0.124 to 0.001 as this changes price as soon as it moves up a cell and the increments are different as it is a different roof is this any clearer , the error seems to be in the match,ceiling part but i cannot sort it
brian

[Pete_UK]

Change the formula in B10 to this:

=IF(H1="YES",(INDEX($B$5:$H$8,MATCH(CEILING(E1*1000-1,250),$A$5:$A$8,0),MATCH(CEILING(B1*1000-1,600),$B$3:$H$3,0))),0)

Note that I have moved the *1000 into the CEILING functions, so now you are subtracting 1 (mm) from those measurements before rounding them up. Note also that the increments within the CEILING functions (coloured red and blue) are now in mm, and if H1 does not equal Yes then the formula returns zero - you could change the final 0 to "" if you wish to have a blank cell.

Hope this helps.

Pete

[bdf1965]

can anyone help with the attached problem i have attached the installation workbook , i am getting the #n/a again is this because some of the values are in hundreds and some are in thousands in the costs , the formula is enclosed, i am so close to finishing my project
regards
brian

[Pete_UK]

I can see the #N/A value in M4 of this latest file, but there is no formula in there. What formula were you using? Where have the cells gone in which you specify the size you are interested in?

Pete