Please post a sample file, e.g. using a subset of your data with sensitive fields removed.

I've attached a screen shot and a subset of the file that I'm using.

In the screenshot you can see i've got 2 summary tables, one is an overall summary and the other is at a placement strategy level. I tried defining CTR before in dataManager and it comes out wrong in the table. I then tried calculating some fields on the fly in the summary table and CTR still comes out wrong. From the screen shot you can see I calculate 'Impression2Quotes', which is calculated using the formula Impressions/Quotes. I take the mean weighed by 'total quotes' and I get the correct value. However for CTR (Clicks/Impressions), I take the mean by impressions but i get the wrong value.

The formula in the screenshot doesn't work because of 0 division. Some values are 0, some are empty in your [Impressions Delivered] column, so you should use IF function to exclude them first
IF[Impressions Delivered]>0, [Clicks recorded]/[Impressions Delivered], " ")

I tried that before, but it doesn't work. When I use the If statement I get a missing, cause there are rows in the underlying data where there are 0 impressions. But it should still calculate for the remaining rows.

By generating formulas in DataExplorer I thought the formulas would be calculated at the aggregation level that the table is at (on the fly). However it still calculates it at the level your underlying data is at and then attempts to aggregate it, which is where the issue arises

IF function will eliminate zeros, missing values, or both, depending on how you formulate it. You can also use one of the Data Manager blocks - Search/Replace to manage this.
Default behaviour for both Omniscope and Excel is that on Mean calculation it will ignore the missing values and include zeros.
When your formula is applied, where division with zero is present, Omniscope will return null (missing value) and will enable you to calculate the rest.
In the example file attached, Mean is calculated on 180 rows [Clicks/Impressions] field, in the Table view, giving the same result as the single Aggregated Mean value.
When weighted Mean is used, [Total Quotes] as weight field, result is different.

I found the option to apply "formula result", which calculates the value on the fly - this is exactly what i was after