#n/a error

can anyone help with the attached workbook problem please , it returns a #n/a error

hi andy
the formula i thought was in the box is
=if(j1="yes",(index($b$4:$k$7,match(ceiling(h1*1000-1,500),$a$4:$a$7,0),match(ceiling(f1*1000-1,500),$b$2:$k$2,0))),0)
brian

also H1 should have 3.6 inserted and F1 should have 3.6 inserted , sorry for lack of info
brian

H1 and F1 are the widths and depth for a conservatory , with these inserted it should give me the cost from the appropriate grid , as soon as they go over the sizes they need to move to the next cost up
brian

i have put the correct table on

thank andy

hi andy ive entered the formula you gave me im getting a #name error in m4 and #ref in p5 any advice please
brian

i had missed the "" of off yes in m4 which would explain the #name error but i am still getting the #ref error in p5 , sorry to be a pain im a bit of a novice at this
brian

the name error was caused by missing the "" of off the word yes , ive tried new formula but still getting #ref error
regards
brian

the name error was caused by missing out """" on yes , even with the new formula i am still getting the #ref error
brian

can anyone tell me why i am getting this error please LEAN TO INSTALLS.xlsx

I see an #N/A error rather than a #REF error....

But Pepe is correct in it errors due to not finding the value your formula specifies.

every time i attach a work book i send the wrong one , i dont know how to get back to attach correct one

Reply to the thread and attach the correct workbook in the same way you attached the one above.

this hopefully is correct one

i have changed the 5 to a 4 in cell p5 but the prices on the grid should change as soon as the sizes go over the sizes in cells f1 and h1 , eg width 6 and depth 3 should give 1272 and not 1200 any idea please

Can you explain why you should be returning 1272? I'm not sure I understand the logic behind what you're trying to do here...

its a costing grid and as soon as a size goes over it has to be costed to next cell , in the grid width 6 depth 3 should return 1272 but i am getting 1200

I worked it out in my thick skull as soon as I hit send...

Try, =INDEX(B4:K7,MATCH(H1,A4:A7,1),MATCH(F1,B2:K2,1))

Does that do what you need?

moving the depth and width sizes is not changing any sizes at all

Works for me...

Have you got "Auto Calculation" switched on?

width 6.6 and depth 4 still returns wrong cost but its better on most

how do i swith that on im a novice at this

if you enter 4.1 width and 3.6 depth it is still wrong

this is driving me nuts can anyone help

thank you so much its been driving me crackers
kind regards
brian

Hi Brian,

no wonder I couldn't help you - you keep starting new threads. You started off with this one:

http://www.excelforum.com/excel-form...d-formula.html

and then moved on to this one:

http://www.excelforum.com/excel-form...ml#post3460539

and then started yet another one:

http://www.excelforum.com/excel-gene...ml#post3460906

and finally started this current thread.

I've lost track of where you are up to - why do you keep changing the units of the grid?

Do you still have a problem to be sorted?

Pete

yes still a problem , i have reversed the table as suggested by martindwilson in the attachment it works perfect , i have copied this formula into my price list and have got the #n/a error back , i have to change the grid sizes for the different types of conservatory we do
brian

Brian,

I've put the various grids that you have posted in one file, one sheet for each grid. I've made these look the same, so that your table headers all start on row 10, and you have the rows above for general info and data input. For any grid, enter your width, depth and the validity (I assume that this is what the Yes is for) in the yellow cells, and you will get the price in the blue cell.

If you need to add any other grids, ensure that the units for height and width in the table are in mm, ant enter the appropriate increments in cells D3 and D4.

I have made the formula in the blue cells extend beyond the tables, so it can accommodate larger table.

Hope this helps.

Pete