This one is pretty straightforward; I found out how to do this while helping someone double-check an assignment. Calculating bubble point and dew point can be extremely tricky by hand if you are given a fixed total pressure P instead of a fixed temperature T, as iterative methods must be used. Luckily, Excel can rapidly perform iterative analysis using the Goal Seek function. The other key is numeric equations for vapor pressure at a given temperature for a wide variety of substances, which can be found on wikipedia and, this person informs me, are derived from solutions and refinements to Antoine's Equation. Simply search "substance (data page)" on Google, and the appropriate Wikipedia page should be the first result. For example, "Benzene (data page)" leads appropriately to Benzene (data page). I suppose you could simply search Wikipedia, but I'm not quite up to the task.
Anyway, in order to do this, you'll need to know three sets of information:
1) The constant pressure P of the mixture
2) The mole percent composition xi (or yi) of each component i of the mixture. These should sum to 1. If not, something is wrong.
3) The equation for Pvapor of each component i, taken from the appropriate Wikipedia page. Note that in my spreadsheet below, I called it P(sat), the saturated pressure. Same thing.
Check out the image below for how I laid out my spreadsheet. I'll explain how it works in a second.
You can see that I am working with a 3-component system; I typed the x/y (that's x-slash-y, not x-over-y) percentages in, the pressure P in atm, and a starting temperature T in C. I also added P in mmHg (760, for this example), and T in deg-K (T in deg-C +273.15). The P(sat) column the sets of equations from Wikipedia, with the T in deg-C plugged in; it returns a value in mmHg. Be very careful to note that the formulae for vapor pressures given on Wikipedia often return either loge(P, mmHg) (aka ln(P, mmHg)) or log10(P, mmHg), so you will need to plug in the formulae to excel as something like exp(formula) or 10^(formula). You can see that my temperature in deg-C is in cell D10; working from the Benzene page, the formula for cell E3, P(sat) of Benzene, is:
Code:
Easy enough, as long as you don't mind the tedium of copying down the formulae from Wikipedia. Anyway, once you have the vapor pressures for each of your components, you'll want the bubble point and dew point. The bubble point formula for each component is (Pi(sat)*xi)/P, where Pi(sat) is the formula for component i we just typed in, xi is the value of the x/y column for that component, and P is the total pressure P in mmHg from the constants table. Note that you should of course be referencing cells instead of typing in constant values, so my Benzene bubble point formula is:
Code:
The dew point formula is similar, (P*yi)/Pi(sat), where yi is once again the value of the x/y column for that component and the other variables are the same cells as for bubble point. Directing below the bubble point and dew point cells, set the cell equal to the sum of the cells above it; you can see that I've marked those cells out with "SUM->".
Now simply goal-seek. To find the bubble point, goal-seek the SUM-> cell in the bubble point column towards 1 by changing the T (C) cell. For the dew point, goal seek the SUM-> cell in the dew point column towards 1, again changing the T(C) cell. Be sure to note down your values, as you can't simultaneously solve for bubble point and dew point with this method.
Good luck, and hopefully this helps you in some way. Let me know if not.
Anyway, in order to do this, you'll need to know three sets of information:
1) The constant pressure P of the mixture
2) The mole percent composition xi (or yi) of each component i of the mixture. These should sum to 1. If not, something is wrong.
3) The equation for Pvapor of each component i, taken from the appropriate Wikipedia page. Note that in my spreadsheet below, I called it P(sat), the saturated pressure. Same thing.
Check out the image below for how I laid out my spreadsheet. I'll explain how it works in a second.
You can see that I am working with a 3-component system; I typed the x/y (that's x-slash-y, not x-over-y) percentages in, the pressure P in atm, and a starting temperature T in C. I also added P in mmHg (760, for this example), and T in deg-K (T in deg-C +273.15). The P(sat) column the sets of equations from Wikipedia, with the T in deg-C plugged in; it returns a value in mmHg. Be very careful to note that the formulae for vapor pressures given on Wikipedia often return either loge(P, mmHg) (aka ln(P, mmHg)) or log10(P, mmHg), so you will need to plug in the formulae to excel as something like exp(formula) or 10^(formula). You can see that my temperature in deg-C is in cell D10; working from the Benzene page, the formula for cell E3, P(sat) of Benzene, is:
Code:
=EXP(LN(760/101.325)-8.433613*LN($D$10+273.15)-6281.04/($D$10+273.15)+71.10718+6.198413*(10^-6)*($D$10+273.15)^2)Easy enough, as long as you don't mind the tedium of copying down the formulae from Wikipedia. Anyway, once you have the vapor pressures for each of your components, you'll want the bubble point and dew point. The bubble point formula for each component is (Pi(sat)*xi)/P, where Pi(sat) is the formula for component i we just typed in, xi is the value of the x/y column for that component, and P is the total pressure P in mmHg from the constants table. Note that you should of course be referencing cells instead of typing in constant values, so my Benzene bubble point formula is:
Code:
=(E3*D3)/$D$9The dew point formula is similar, (P*yi)/Pi(sat), where yi is once again the value of the x/y column for that component and the other variables are the same cells as for bubble point. Directing below the bubble point and dew point cells, set the cell equal to the sum of the cells above it; you can see that I've marked those cells out with "SUM->".
Now simply goal-seek. To find the bubble point, goal-seek the SUM-> cell in the bubble point column towards 1 by changing the T (C) cell. For the dew point, goal seek the SUM-> cell in the dew point column towards 1, again changing the T(C) cell. Be sure to note down your values, as you can't simultaneously solve for bubble point and dew point with this method.
Good luck, and hopefully this helps you in some way. Let me know if not.
My hesitation to provide the original spreadsheet is that I think I don't actually have it any more. As the first post suggests, you can look up those three substances on Wikipedia, find the relevant equations, plug them in as equations, and then add the bubble-point and dew-point formulae and perform your goal seeks.