[Solved] OpenOffice Calc How To...

Discuss the spreadsheet application
Post Reply
meatball81
Posts: 2
Joined: Sun Oct 16, 2016 12:08 pm

[Solved] OpenOffice Calc How To...

Post by meatball81 »

Hello! Sorry, but I am an excel user switching over to Open Office, and I have figured out 99% of how to use it, but there is one thing I am trying to do which I cannot figure out...

I manage a retail store selling appliances and lawn and garden items. On some of the bigger ticket items, we offer an extended warranty. I created a spreadsheet to show myself and my associates where they stand in product sales and warranty sales. What I need to know is how to get a percentage from two numbers.

For instance, if my associate, let's call him Joe, sells 43 washing machines and sells 16 warranties, I want the result in the PA CLOSE RATE field to be the percentage of warranties sold by units. For example, using the above numbers, Joe's closing rate would be 37.21%... 16 times 100 divided by 43. This is what I cannot figure out how to do. Also, my chart figures out their WARRANTY % VS WARRANTY $. This would be the same formula as above, if I could just find it... For instance, Joe sells $2,000 worth of merchandise and $500 worth of warranties. Joe's warranty percentage is 25%.

I have been manually doing this every single day, and it is getting kind of tedious, so it would be nice if I could figure out how to us formulas to do this automatically, so all I have to do is change the field in question and the program would do all the math automatically.

Sorry this copied and pasted so badly, the real chart looks much better, but you can see what I mean, I hope. If you look at the line that says 26 Laundry for instance, I want to be able to change the 56 to a 57 make that close rate figure itself out rather than me having to do it on a calculator. Same thing if I change the Warranties column. Also, if I change the SALE $ and/or the WARRANTY $ columns, the Warranty % column should change by itself. (as well as the TOTALS on the last line, but if I could figure out the formula I am asking about, I will be able to fix that without assistance.)


Associate : 1008 August – October 2016
JOE UnitsWarrantiesClose RateSales $ Warranty $ Warranty %
26 Laundry 56 30 53.57% $27,404.00 $5,469.00 19.96%
22 Dishwashers 10 3 30.00% $4,882.00 $380.00 7.79%
22 Hoods / OTR Micro 7 5 71.43% $1,643.00 $196.00 11.93%
22 Ranges/Wall Ovens/ CT 19 10 52.63% $10,040.00 $1,469.00 14.63%
42 AC/Water Heater/Water Softener/Dehum 17 3 17.65% $6,172.00 $315.00 5.10%
46 Refrigerators/Freezers 32 13 40.63% $21,362.00 $2,114.00 9.90%
71 Tractors 3 0 0.00% $6,095.00 $0.00 0.00%
71 Mowers/Snow Throwers/Tillers 8 2 25.00% $3,700.00 $320.00 8.65%
6/9/71 Other 6 4 66.67% $1,444.00 $262.00 18.14%

TOTALS 158 70 44.30% $82,742.00 $10,525.00 12.72%

If anyone out there can help me figure this out, it would be greatly appreciated!
Attachments
Example.ods
Here is the real chart if the one I copied and pasted doesn't make sense
(12.97 KiB) Downloaded 76 times
Last edited by Hagar Delest on Tue Oct 18, 2016 10:10 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4.1.2 / Windows 7
jrkrideau
Volunteer
Posts: 3816
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: OpenOffice Calc How To...

Post by jrkrideau »

I think you want something like the attached sheet. Note that your calculation of the mean (average) for the Close Rate and the Warranty % strike me as non-standard.

For example you appear to be dividing Total Warranties by Total Units. In your context this may make the most sense but if you take the mean of the calculated Close Rate you get a different result (see numbers below the summation line).
Attachments
Warrants.ods
(14.01 KiB) Downloaded 77 times
LibreOffice 7.3.7. 2; Ubuntu 22.04
Alex1
Volunteer
Posts: 723
Joined: Fri Feb 26, 2010 1:00 pm
Location: Netherlands

Re: OpenOffice Calc How To...

Post by Alex1 »

It seems to me that meatball's calculation of the totals percentages are more useful because they take the numbers of units into account. Columns D and G of Warrants.ods should be formatted as percentage.
AOO 4.1.15 & LO 24.2 on Windows 10
meatball81
Posts: 2
Joined: Sun Oct 16, 2016 12:08 pm

Re: OpenOffice Calc How To...

Post by meatball81 »

Thanks guys, I figured it out. All I had to do was input =C5/B5 in the CLOSE RATE field and then format the entire column to display in % Thanks for you help
OpenOffice 4.1.2 / Windows 7
Post Reply