Formula to workout the Total margin from a table with Gross amounts

[samyraj]

Hi

I have a table with multiple rows giving details of the gross amount and margins, what is the best function to use to find the total margin without having to compute the details in another column and get the total margin

I have attached a sample data set

Thank you for your help in advance

Regards

[AliGW]

Are you still using Excel 2019?

Try this:

=ROUND(SUMPRODUCT(H5:H8*G5:G8),2)

[kvsrinivasamurthy]

Try

=SUMPRODUCT(E5:E8*((1/(1+(1/G5:G8)))))

[samyraj]

Apologies AliGW I should you have mentioned that Coloumn H & Coloumn K are my workings and they do not form part of the table

Thanks kvsrinivasamurthy , the formula worked fine, unfortunately for me there were few line items with 0% margin and the total showing an error message. Apologies for not mentioning this earlier , is there a fix for same, I have attached the revised excel

[kvsrinivasamurthy]

Change the formula

=SUMPRODUCT(E5:E8*(G5:G8/(G5:G8+1)))

[samyraj]

Thanks a lot kvsrinivasamurthy, it was a huge help and much appreciated.

[kvsrinivasamurthy]

Welcome. Have a great day.

[samyraj]

Thanks , I am posting another similar question regarding a calculation and would appreciate if you could also please help me with same if possible