No worries about questioning my methods, I could still be wrong with my thinking. The next few weeks should sort that out for me.
There is no single conversion factor between lux and W/m2; there is a different conversion factor for every wavelength, and it is not possible to make a conversion unless one knows the spectral composition of the light.
So the key thing is if you have to know the spectral composition otherwise known as the spectral power distribution (SPD) in order to make the calculations. And they are made on a per wavelength basis which is what my spreadsheet does.
This all started for me when I ran across this brilliant article where the author details the math behind making this conversion. Do a search of:
Photometry and Photosynthesis by Ian Ashdown
His equations makes this conversion for a known spectral power distribution. It occurred to me that I could maybe use an inexpensive multichannel color sensor to measure the SPD of any light source. With only 6 channels it will be somewhat limited in resolution but it could do a fair job. The 11 channel sensor I am getting this week should do even better.
This is one area where my limited math skills breaks down. Right now I am just interpolating between the 6 wavelengths it reads to give a full spectrum readout at 1 wavelength intervals across the 400-700nm range. I am researching Gaussian functions which the channel outputs of the sensor resemble, but my poor old brain can only handle so much though.
My thinking at this point is to calibrate the sensor using light that has a nice smooth curve through the PAR spectrum. Which is why I started with tungsten bulbs. It has a nice, well known SPD curve.
I am not a scholar so look over me if I confuse some terminology, it is tricky stuff. I don’t pretend to know how the sensors on Lux meters or even Par meters work. My thoughts are a single sensor under the white dome outputs a current in proportion to the total intensity of light that strikes the sensor. I don’t know about a Par meter but a Lux meter filters the light in such a way that it mimics peoples perception of light intensity using the Luminous efficiency function.
This is from the data sheet of my Lux meter:
It gets a little weird below 500 but otherwise looks like a good response. My particular meter, about a $100 has an accuracy to within 5%. Don’t quote me but I think that is close to the accuracy of some of the Apogee Par meters. Point being the best I can hope for is 5% and will be happy with 10% error.
So getting back to calibrating using a Lux meter and Luminous efficiency function, here is my sensor measuring incandescent light with the Luminous efficiency function (red dashed line) on the same graph:

So my thinking is if I am going to use a Lux meter to calibrate my sensor, then firstly I have to calibrate it to measure measure Lux. Once it is power calibrated it should be a matter of not running the output through the Luminous efficiency function. Then it would be measuring the total number of photons under any SPD function.
I have to admit, I just barely understand all of this and the more I work with it the more it makes sense. But it is too early for me to say yes, this works. Tomorrow I could notice something that throws a curve ball at me and I have to scrap this whole method.
Which is fine if that happens. The journey has been fun!