Help with a variable (I think) formula

[*kev*]

Hi,

I'm hoping that someone may be able to help me with what I thought was quite simple, but I just can’t get my head around what I need to do, as it's at the limit of my excel knowledge and experience.

I trying to create a cost calculator

the issue that I have is that product 1 contains strength at 10mg/ml and product 2 contains strength at 20mg/ml, if the weight of a person changes the amount of product a person needs changes, each bottle contains the same volume i.e. 1ml and a person weighing 1 kg would take 1 bottle of product 1 but would need 0.5 of a bottle of product 2, this would still equate to one bottle though.

My question is, what formula would I need to use in H6 & H8 to help to work this out, I have attached the spread sheet I am using to give an idea of cell locations etc.

[JohnTopley]

Post a file on this forum rather than a file-hosting site.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[MrShorty]

This one feels to me like it first ought to be a math problem before it becomes a programming problem. It also feels like a fairly standard "distance=rate * time" type problem.
EQ 1) total dose = dose rate/kg * total body weight
EQ 2) number of bottles = product strength * total dose.

You can then substitute EQ 1 into EQ 2 to get a single equation. Assuming you are given dose rate, body weight, and product strength, you can then directly calculate number of bottles. If desired, you could calculate EQ 1 separately in Excel, then use the result to calculate EQ 2.

Does that help?