#### KamalS

##### New Member
Sorry I don't have excel on this computer to check the data, but the rounding is done in excel. The data in my post #118 was cut and pasted from the spreadsheet.

Excel keeps the data in a floating point format (which I mentioned) and then you select "column number formatting" which is applied to the native number and then *displayed* truncated. In that column.

So for this row;
Code:
23   165.6   0.248689887   59.0   56.5   56   56
The actual value may have been 56.47000 which excel rounded up (for display) to 56.5 as that is the correct rounding to 0.1 resolution, then when asked to round it to integer 56.47000 was properly rounded down to 56.

Personally I hate Excel, it's so "Microsoft" in that it hides things from the user and does things that it wants regardless of what the user wants. That's part of the reason I have supplied the spreadsheet calcs in text form.
I use Excel 2010 at work and it seems to have a much more accurate maths engine than the version you are using. Hence, I have significant deviation after the first decimal point of the table entry and it goes all downhill from there.

This is the reason why I told you I will be coding the tables up in Python (I for one do not have Excel at home) as that is much more portable.

That answers your question 1. Just to get Excel to display the sine graph correctly requires pasting the integers into notepad and back into Excel, to make them real integers. Otherwise Excel keeps charting the original non-rounded data. If you know a way to force it to truncate the native data please tell me, as I don't use it often enough to have found out that trick.
I rarely use Excel too for my computation needs, but I am wondering how you got the last 'Int' column.

If you mapped using INT(), you should be able to project a bar chart on this column and have it use the integer instead of the floats.

I will share the Python code with you and this forums soon but I also had some more questions:

1. Are the formulae compatible to have the step size changed from 50 to something else like 64?

2. Why are you scaling the output for a peak-to-peak amplitude of 72?

3. What is that 200 value?

#### Mr RB

##### Well-Known Member
I use Excel 2010 at work and it seems to have a much more accurate maths engine than the version you are using. Hence, I have significant deviation after the first decimal point of the table entry and it goes all downhill from there.
Do you have an example? It's hard to believe my old version Excel would do such simple math calcs "wrong". I deliberately kept it to the simplest math possible, multiply abnd divides apart from what was necessary to get the sin() working.

...
If you mapped using INT(), you should be able to project a bar chart on this column and have it use the integer instead of the floats.
Thank you! That should get it to display the data in integer. I'll try that later, like I said I don't use Excel much I would use it probably less than an hour a year.

It still annoys me that I tell it to "format" the column to integer (no dec point) and then I tell it to "chart" that same column it displays the original float data. Microsoft logic bah!

...
1. Are the formulae compatible to have the step size changed from 50 to something else like 64?
Yep I thought that was pretty obvious, just change the /50 in this cell to /64;
C7 Degrees sine degrees = C6 + (360 / 50)

...
2. Why are you scaling the output for a peak-to-peak amplitude of 72?
To avoid hardware issues with using very small PWM values, amplitude 72 allows a min PWM of 14% and max pwm of 100-14%. Amplitude 72 provided a decent tradeoff of enough resolution and signal amplitude while avoiding extreme PWM ratios.

...
3. What is that 200 value?
I assume you mean here;
F7 Comp adds PWM compensation= E7 + (((E7+E8)/200) * (E8-E7))
It is adding a small value in interpolation between the table values for E7 and E8, so it's really /2 (to get the average of E7 and E8) and /100 to get it normalised to PWM percentage points. I used /200 thinking it was inherently obvious, sorry about that.

You seem to be getting right into this, what was your goal again? From your earlier posts I thought you were aiming to build a 20kHz sinewave generator or something?

(edit) Actually, are you sure that INT() will do the job? Won't that just round each number DOWN to the integer? Maybe that's why your Excel is giving the wrong numbers after rounding?

Last edited:

#### KamalS

##### New Member
Do you have an example? It's hard to believe my old version Excel would do such simple math calcs "wrong". I deliberately kept it to the simplest math possible, multiply abnd divides apart from what was necessary to get the sin() working.
Your Excel sheet seems to be weird. Consider these values that you shared (I left out some 3 columns towards the end)

Code:
-------------------------------------
Step	Degrees	Sine value      Table
4	28.8	0.481753674	67.4
6	43.2	0.684547106	74.7
13	93.6	0.998026728	86.0
14	100.8	0.982287251	85.5
33	237.6	-0.844327926	19.5
35	252	-0.951056516	15.7
36	259.2	-0.982287251	14.5
The formula you use to calculate 'Table' from 'Sine value' is:

Code:
=(72 / 2) + (<Sine value> * (72 / 2)) + 14
Plugging in the 'Sine value' for each of the above we get:

Code:
=(72 / 2) + (0.481753674 * (72 / 2)) + 14 = 67.343132264
=(72 / 2) + (0.684547106 * (72 / 2)) + 14 = 74.643695816
=(72 / 2) + (0.998026728 * (72 / 2)) + 14 = 85.928962208
=(72 / 2) + (0.982287251 * (72 / 2)) + 14 = 85.362341036
=(72 / 2) + (-0.844327926 * (72 / 2)) + 14 = 19.604194664
=(72 / 2) + (-0.951056516 * (72 / 2)) + 14 = 15.761965424
=(72 / 2) + (-0.982287251 * (72 / 2)) + 14 = 14.637658964
Do you see what I mean?

Compare the last column values of the table I quoted with each value above.

Whatever rounding scheme your Excel uses is wildly erratic across rows:

67.343132264 can be changed to 67.4 if a ceiling function is used.
85.362341036 can be changed to 85.5 only if something is horribly wrong
15.761965424 can be changed to 15.7 if a floor or truncating function is used.
14.637658964 can be changed to 14.5 only if something is horribly wrong

This does not make sense at all if you are using the same rounding mechanism down a column.

It still annoys me that I tell it to "format" the column to integer (no dec point) and then I tell it to "chart" that same column it displays the original float data. Microsoft logic bah!
I would steer away from formatting the display of cells while exchanging data sets

It makes it easy to view for personal use, but getting different versions of excel to reproduce the same value could be an issue I would like to stay away from!

To avoid hardware issues with using very small PWM values, amplitude 72 allows a min PWM of 14% and max pwm of 100-14%. Amplitude 72 provided a decent tradeoff of enough resolution and signal amplitude while avoiding extreme PWM ratios.
I would like to understand this a bit better - is this your experience speaking?

Is there some documentation I can read to come to a similar conclusion?

You seem to be getting right into this, what was your goal again? From your earlier posts I thought you were aiming to build a 20kHz sinewave generator or something?
Correct.

I was thinking of a DDS based design, but that would keep the CPU pretty busy unless I used an external DDS module.

Since most PICs I use have an independent PWM module, using the PWM for DAC is a good way to free some CPU letting me do some computation.

I appreciate the time you are putting into this.

#### Mr RB

##### Well-Known Member
Your Excel sheet seems to be weird. ...
The formula you use to calculate 'Table' from 'Sine value' is:
Code:
=(72 / 2) + (<Sine value> * (72 / 2)) + 14
Plugging in the 'Sine value' for each of the above we get:
Code:
=(72 / 2) + (0.481753674 * (72 / 2)) + 14 = 67.343132264
=(72 / 2) + (0.684547106 * (72 / 2)) + 14 = 74.643695816
=(72 / 2) + (0.998026728 * (72 / 2)) + 14 = 85.928962208
=(72 / 2) + (0.982287251 * (72 / 2)) + 14 = 85.362341036
=(72 / 2) + (-0.844327926 * (72 / 2)) + 14 = 19.604194664
=(72 / 2) + (-0.951056516 * (72 / 2)) + 14 = 15.761965424
=(72 / 2) + (-0.982287251 * (72 / 2)) + 14 = 14.637658964
Interesting! I'm not sure what mechanism Microsoft used for rounding the data, I just clicked the whole column and told it "number format, 1 decimal place". I'll check later as the Excel thing went through many variations during the project and there may have been something left over from my earlier work that affected those numbers.

Either way I'm really not stressed about the 0.1 digit rounding as the base resolution of the whole system is to the integer digit anyway. Anything within about half an integer digit is good enough and will be largely removed by the hardware filter.

...
I would steer away from formatting the display of cells while exchanging data sets

It makes it easy to view for personal use, but getting different versions of excel to reproduce the same value could be an issue I would like to stay away from!
That's a good point! My earlier version used manual compensation for the digit rounding, then I found Excel interfered with that was was messing up my nice manual rounding so took the manual rounding out. If I had more experience with Excel or a version newer that 1996(!) maybe it would work better.

...
re what I said about min/max PWM;I would like to understand this a bit better - is this your experience speaking?

Is there some documentation I can read to come to a similar conclusion?
Yes, and I am sure there would be some documentation somewhere. I thought this was one of the basics of PWM... The PWM resolution is 200nS which is a very short period. If the output hardware has some characteristic of inductance or non symmetrical rise/fall times or some delay of rise or fall times this can cause issues with very short PWM pulses. This could cause a potential "clipping" of the top or bottom or both of the generated signal.

It may not be strictly necessary in this particular design but I thought it best to centre the sine waveform around the 50% PWM mark and avoid extreme PWM values, and had enough amplitude at 72% to achieve the objective. Good design is usually about trade-offs, not about specifics.

...
I was thinking of a DDS based design, but that would keep the CPU pretty busy unless I used an external DDS module.

Since most PICs I use have an independent PWM module, using the PWM for DAC is a good way to free some CPU letting me do some computation.

I appreciate the time you are putting into this.
If you really want a 20kHz sine then as I said before PWM seems a much worse option than using a simple DAC, like an R2R ladder or cheap parallel input DAC IC.

PWM just won't give the desired resolution at the high frequencies and to get a good sine will require some tuning of the filter to the specific frequency. If you used a DAC output speed increases many times over and filtering becomes much less critical.

Good luck with it but I still think you are choosing a hard way to do that task when there are easier ways!

#### KamalS

##### New Member
Interesting! I'm not sure what mechanism Microsoft used for rounding the data, I just clicked the whole column and told it "number format, 1 decimal place". I'll check later as the Excel thing went through many variations during the project and there may have been something left over from my earlier work that affected those numbers.
Thank you. If you could, you can also send me the original dataset as a CVS file or a text file as well.

Best would be, of course, if you could PM me your Excel 97 sheet

Good luck with it but I still think you are choosing a hard way to do that task when there are easier ways!

Do you suggest that I use a DDS based design anyways?

You know that would keep the CPU pretty busy mapping the phase to word output and the uC would have very little resources to do anything else.

However, if you still think that is a pretty good idea - let me ask you this: Why not use something like the AD5933 to make a LC meter? (I have a few reasons, but will share later)

#### Mr RB

##### Well-Known Member
...
Do you suggest that I use a DDS based design anyways?

You know that would keep the CPU pretty busy mapping the phase to word output and the uC would have very little resources to do anything else.

However, if you still think that is a pretty good idea - let me ask you this: Why not use something like the AD5933 to make a LC meter? (I have a few reasons, but will share later)
I think your goal of a microcontroller based function generator is quite far from this project and has a whole different set of requirements and problems...

Really the best thing would be to start a thread saying what you want to make, with all the details and specs etc and let people suggest options of the best way to go about it. It may well be that the best option is to use a micro controlling a dedicated DDS chip, provided you don't mind the cost and construction difficulties and the fixed resolution. Personally I would probably try doing in all inside the PIC chip, because I like single chip cleanliness for hobby projects even if it's not always the easiest or best way.

My opinion is STILL that for a decent function generator to 20kHz, PWM is not the way to go, and then most of this thread is not of much use to you.

Last edited:

#### KamalS

##### New Member
In any case, here is the file and you can run it in LT spice and check out the two responses Vo1 and Vo2 and of course view the Fourier components.

This pattern should be exact (no cheating he he) except the rise and fall times are 1e-7 which should probably be shorter than that, like 1e-8 or something, but we can fix that easily if you want to see the difference in LT spice.

Oh yeah, note that this isnt the simplest way to generate MrRB's pattern, but it does in fact work so what the heck
I wanted to give this a try, but before that had a few questions:

1. I would like to see how increasing the number of samples (MrRB currently uses 50, and I am thinking of using 128 or more) would affect the THD

What changes would I need to make to the .asc file to review the different in performance?

2. I would like to see how increasing the resistance in the RC filter change the peak-to-peak output from the PIC pin.

What changes would I need to make to the .asc file to view the outcome?

MrAl - Thanks again for sharing.

Last edited: