Help finding an error and thoughts on streamlining

[N0t Y0urs]

This is a link to the working file. Workings.xlsx

My issue is that my totals don't equal with the formulas on the first page.

Additionally I would like to edit the formulas so I don't have all the doubled up data.

The summary section at the top in the range 1:10 rows gives a different total to that from rows 15 down

 

[Michael M]

Firstly, most forum members won't simply open a link to a workbook !!
Please try and explain in detail what your are trying to achieve AND have a look at using the XL2BB download (see my tag for the download) to attach sample data to the forum

 

[N0t Y0urs]

Thanks Michael, however the file is 7 sheets with over 500 rows of data and 200 columns of data so making a smaller version is near impossible. But thanks for the suggestion.

However, further to this my previous post wasn't complete as I explained it call and it posted before I wanted it to and I thought I had saved a draft of it.

But don't worry about it I will figure out where the errors are myself and if I can't I will simply do it the long way and know that it is correct rather than over estimating the forecast which is completely wrong because the main formulas are the issue. I have considered pulling only a small sample out however due to the nature of the file I really need the extended version or a smaller option is to actually only have half theta because my formulas all reference each other and are dependent on each other. But as I said will go back to the drawing board and do this the long way because what I have now is wrong.

 

[Michael M]

Can you upload the file to Dropbox or a similar hosting site...with an explanation of what the problems are ?

 

[N0t Y0urs]

Thanks Michael, I am pulling my hair out here but what I have decided is to tackle one issue first and foremost as that is causing me headaches so this is the first of many

This is my current formula, (this is the main recurring formula as well so if there is a simpler, cleaner solution I am certainly all ears)

'=SUMPRODUCT(K7*Tables!$I$9:$I$12*Tables!$I$5:$I$8*10*VLOOKUP(VLOOKUP($J7,Tables!$B$4:$C$42,2,),Tables!$E$4:$F$24,2,))

So this is referencing my tables page but it is giving me the incorrect answer. What I thought it was doing was not subtracting the Level 1 and 2 percentage off the calculation but it isn't. The answer should be $13.54 not $22.70 that my playing spreadsheet has or the $17.80 that my actual working spreadsheet reports.

Is that clearer and honestly thanks for the assistance

Book2
	B	C	D	E	F	G	H	I	J	K	L	M
2
3	Info & Criteria
4
5	Raw Price	1.41
6	Mulitpler	10
7	Size	0.04		Calculations - what it should be
8
9	1	12.5		0.01		$ 1.76		0.01	$ 1.76
10	2	18.5		0.01		$ 2.61		0.01	$ 2.61
11	3	32.5		0.02		$ 9.17		0.04	$ 18.33
12	4	125		0		$ -		0	$ -
13	Level 1%	10%
14	Level 2%	10%				$ 13.54			$ 22.70
15	Level 3%	80%
16	Level 4%	0%
17
18
19	=SUMPRODUCT(K7*Tables!$I$9:$I$12*Tables!$I$5:$I$8*10*VLOOKUP(VLOOKUP($J7,Tables!$B$4:$C$42,2,),Tables!$E$4:$F$24,2,))
20
Sheet1

Cell Formulas
Range	Formula
I9:I12	I9	=ROUNDUP($C$7*C13,2)
J9:J12	J9	=$C$5*$C$6*$I9*$C9
E9:E10,E12	E9	=ROUNDUP($C$7*C13,2)
E11	E11	=ROUNDUP($C$7*C15,2)-E9-E10
G9:G12	G9	=E9*$C$5*$C$6*C9
G14,J14	G14	=SUM(G9:G13)

Here is the Dropbox link but it is not necessary as I think I have summarised what I am doing and where my issue is. Mr Excel The other link was Google Drive but I did restrict the option to view only thinking it would be safer fall all just to see rather than edit but I could be way off here

 

[N0t Y0urs]

I have given up trying to find the solution but have decided to go with an additional table with index match as my solution