Sidebar Sidebar
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

Help needed with using Excel sheets online

T

torndownunit

macrumors regular
Original poster
Jan 4, 2009
242
0
Hello.

I am doing some web design work for a client who has a 'calculator' built in Excel incorporated into the site. It's a fuel consumption calculator.

What is the best way to go about this? I guess I basically want to Excel sheet to be a web application.

I have very limited experience with any type of programming. My experience with Excel is even less, but everything is written already on that end. My experience consists of modifying some pre-written scripts to suit clients needs. So I am trying to find out if this is something I can do myself, or if I need to find someone else to do this.

Any advice is greatly appreciated. Thanks in advance.
 
Use Google Docs :D

You sound like you're out of your league on this, so it might be best to get an additional person to help out. There are some spreadsheet web apps out there that you can incorporate into a site, but a lot of them cost money. You can Google to find some of them. It's hard to give specific help since you haven't stated the current framework and what it is you're plugging into and need to work with.
 
Angelwatt:

Your advice to check out Google Docs was great, but I also found this which the OP might want to check out:

The Google Spreadsheets Data API allows client applications to view and update Spreadsheets content in the form of Google Data API feeds. Your client application can request a list of a user's spreadsheets, edit or delete content in an existing Spreadsheets worksheet, and query the content in an existing Spreadsheets worksheet.
Click to expand...
@OP:

Let me preface this reply by stating, clearly and for the record, even THIS solution, which is a mashup using Google's spreadsheet API (advanced programming interface), is not for novices brand new to development. But it's not guru status either. It simply requires a solid foundation in API programming, whatever platform you have (PHP, ColdFusion, ASP, .Net, etc.) and medium knowledge in your platform.

The "other" way of doing it is finding web software that replicates the behavior of Microsoft Excel, which as noted by angelwatt is usually commercial software - usually expensive - and very proprietary. API's are intended to replace those old, expensive dinosaurs --- with API the server on the other side does most of the work. It's a new Internet, API's RULE in terms of rich web 2.0, i.e. a dynamic and interactive web site that more resembles desktop apps than what we're all used to seeing in simple tables, resource hogging applets, Flash that is close but not perfect, etc.

For details on the powerful, cool, extremely kick-ass Google spreadsheet API, read the entire documentation and then for a beginners tutorial on API, read this to grasp the concept of an API, public vs. private access, how it works, etc. in general.

This specific spreadsheet API by Google is fantastic, and IS your best overall solution if YOU want to develop it. Google docs is perfect if you don't need to HOST it or integrate it in a custom manner, and buying commercial software is perfect if budget is okay to buy software, but not enough to pay for in-house development (or your time).

-jim
 
I figured there was an API for the Google Spreadsheets, but didn't feel like searching it out. Also, part of me thinks it may be overkill. The OP mentioned this being for a fuel consumption calculator, which doesn't necessarily need an entire spreadsheet. They might be able to manage with a decent form and some JavaScript, but not sure since the specifics weren't mentioned. That's why I tend not to go into too much detail in responses until I the poster can fully explain their situation as too many people post too broad of questions with little to no context. And with posts from new accounts it's hard to say if they'll even return to see our posts. I'm rambling though, so I'll wait and see if the OP returns.
 
Just so you know, my response was based in this statement by the OP:

I guess I basically want to Excel sheet to be a web application.
Click to expand...

That's what the API does, of course.

Now if it turns out they want a fuel distance estimator, I think I found an API that might work:

http://www.multimap.com/apidocs/1.2/web_service/ws_routing.htm

View table 4 on that page, there are variables for emissions, fuel type, amount burned, distances, etc. API's are all about saving time and effort for developers, plus most licenses are low cost or free as noted before. So even if the API's I suggested don't work out, it's just a great tool that the OP should be made aware.

-jim
 
Thanks for the tips guy. Sorry for the slow response, but I was away on vacation.

As suggested earlier in the thread, I ended up passing this part of the site onto someone who specialized in programming.

As angelwatt mentioned, there may be an easier solution for what this guy needs, but I don't have the knowledge in this area to find it.

In case it does bring about any more suggestions though, I will give a few more details. It is basically a fuel consumption calculator, but one extra variable would be a 'before and after' after using their fuel additive. The 'savings' with the additive are based on an average % achieved. That % I believe is 16%

The calculator they use to come up with the calculations to put on their sell sheets and flyers are done in Excel and tailored to each company. Whether it needs to be in Excel for web use I have no idea. But I will give a breakdown of their current form. It starts of with these fields:

litres of diesel fuel used in one year (by company)
average price per litre of fuel

Submit button

The results show:

Savings in litres
Savings in $
Savings per day in $

Again, I believe the number used to calculate the savings is 16%​

I am assuming just showing the first 2 fields would be enough for web use. Their excel sheet goes into way more detail, but the online version would just need a basic calculation.

Thanks for the help so far people. I asked this question on a couple of other forums and barely got any replies. Never mind any replies this detailed. You have been a huge help so far.
 
I threw together a quick JavaScript-powered page that shows how you can do a calculator for this. The form also points to a PHP page, which you could use to also do the calculations for visitors who do not have JavaScript enabled on their browsers. The math may not be entirely correct as I put t together really quick and not completely sure about the setup. This is also just an example so you can lay out the page differently. This simply shows what you can do with some simple JavaScript. Just copy and paste the below code into a plain text document and save it with a .html extension and try it out in any browser. If you use TextEdit to do this, make sure to switch it over to plain text mode (as opposed to rich text) before pasting it in, otherwise you'll just see the code instead of the page.

Just let me know if you have any questions about the code.
HTML: 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>Gas Savings</title>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<script type="text/javascript">
//<![CDATA[
function GasSavings()
{
  var additiveModifier = .16;
  var litres = document.getElementById('litres').value;
  var price = document.getElementById('price').value;
  var txt = '';
  txt += "Savings in litres: "+ litres*additiveModifier +"<br />";
  txt += "Savings in $: "+ (litres*price*additiveModifier).toFixed(2) +"<br />";
  txt += "Savings per day in $: "+ (litres*price*additiveModifier/ 365).toFixed(2);
  document.getElementById('results').innerHTML = txt;
  return false;
}
//]]>
</script>
<style type="text/css" media="all">
p {
 margin: .2em 0; padding: 0;
}
</style>
</head>
<body>
<form id="gascalc" action="gascalculator.php" method="POST"
onsubmit="return GasSavings();">
<fieldset>
  <legend>Gas Additive Savings Calculator</legend>
    <p><label for="litres">litres of diesel fuel used in one year (by
    company)</label>
    <input type="text" id="litres" name="litres" /></p>
    <p><label for="price">average price per litre of fuel</label>
    <input type="text" id="price" name="price" /></p>
</fieldset>
<p><input type="submit" value="Calculate Savings" /></p>
</form>
<div id="results"></div>
</body>
</html>
 
Oh wow thanks a ton. I am just on my way to a meeting, but when I get back I am going to go through this. I will update the thread.
 
I had a chance to try the calculator out and it works great! But, it seems I must have missed a variable somewhere that affects the $ savings totals. The litres totals are correct.

I think it's the cost of the product itself I missed. And that affects both the $ totals in the calculator. Here are the results from the spreadsheet using 6,600,000 litres and $1 per litre as the entries.

savings in litres: 1,056,000 (correct on the JS calculator)
savings in $: $785,787
daily savings: $2,153​

On the Excel sheet there is a break down:

Existing cost of fuel for one year: $6,600,000
Cost of Fuel using additive (16% reduction): 5,544,000
COST OF ADDITIVE (1 year retail price): $270,213
Net Cost of Fuel + Addtive for one year: $5,814,213

Total Fuel Cost Savings: $785,787​


You are being extremely helpful, and I don't want to be a pain. Is this something that can be easily tweaked in the JS calculator code?

From looking at your code I have an idea of what is going on, and I think I could tweak it in the future (for new costs etc.), but adding variables is out of my league. I greatly appreciate any help you can give.
 
OK, I fixed the calculations. I was surprised I had even one right. Below is just the JavaScript function as that was all that got changed. I broke the calculations out more so it's easier for you to follow and to update if need be.
PHP: 
function GasSavings()
{
  var additiveModifier = .16;
  var additive  = 270213;
  var litres    = document.getElementById('litres').value;
  var price     = document.getElementById('price').value;
  var savLitres = litres * additiveModifier;
  var cost      = price * litres;
  var netCost   = cost * (1-additiveModifier) + additive;
  var savCost   = cost - netCost;
  var txt = '';
  txt += "Savings in litres: "+    savLitres +"<br />";
  txt += "Savings in $: "+         savCost +"<br />";
  txt += "Savings per day in $: "+ (savCost/365).toFixed(0);
  document.getElementById('results').innerHTML = txt;
  return false;
}
 
Thanks a ton. I got it incorporated into the design and it's working great. You've been a huge help.
 
Ok, well it looks like I was missing another variable. I just don't know much about Excel and am horrible at math, which makes things even harder.

The amount of additive needed (cost of additive per year) changes by the amount of fuel it's being added to. Which makes sense. I didn't even think of it. I gave you a set amount.

I know this is taking up your time angelwatt, so if it's getting too involved I understand. But if you feel like taking a look at this spreadsheet, it explains the calculations. I think there is just 1 or 2 missing from the the JS calculator. http://www.sideeffectsdesign.com/test/enviroaces/consumption.xlsx

Again if it's too much, no problem. The help so far has been greatly appreciated. I was going to send a private message, but it doesn't look like that is set up in your profile.
 
torndownunit said:
Again if it's too much, no problem. The help so far has been greatly appreciated. I was going to send a private message, but it doesn't look like that is set up in your profile.
Click to expand...

Might be the newness of your account that you couldn't send the PM. I've gotten some from others recently. I downloaded the spreadsheet and will give it a look soon. Doesn't look it'll take long to make the change.
 
I've actually been on the forums a lot off and on over the years, but lost my account info awhile back. So ya, I have a new account. Maybe there is some kind of post limit on PM's or something. I am not sure. I get no option to PM you or email you when I click on your name, or go to your profile though.
 
Here's the updated function. The math is spelled out in a bit of detail so should be easy to follow. The math could be cleaned up, but that's up to you. I was getting to same results from this as the spreadsheet showed. The output isn't that pretty, but is has all the info. Also, I got your PM so that's working now.

PHP: 
function GasSavings()
{
  var additive     = .16;
  var litres       = document.getElementById('litres').value;
  var price        = document.getElementById('price').value;
  var savLitres    = litres * additive;
  var cost         = price * litres;
  var costFuel     = litres * (1 - additive);
  var costAdditive = costFuel / 7570 * 369;
  var costNet      = costFuel + costAdditive;
  var costSavings  = cost - costFuel - costAdditive;
  var txt = '';
  txt += "Savings in litres: "+    savLitres +"<br />";
  txt += "Existing Cost of Fuel for 1 year: $"+ cost +"<br />";
  txt += "Cost of Fuel using ACES II: $"+ costFuel +"<br />";
  txt += "Approx. Cost of ACES II for 1 year: $"+ costAdditive.toFixed(0) +"<br />";
  txt += "Net New Cost of fuel and ACES II for 1 year: $"+ costNet.toFixed(0) +"<br />";
  txt += "Total field cost savings per year: $"+ costSavings.toFixed(0) +"<br />";
  txt += "Savings per day: $"+ (costSavings/365).toFixed(0) +"<br />";
  document.getElementById('results').innerHTML = txt;
  return false;
}
 
Per our discussion: Here's the new page with new styling.

HTML: 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>Gas Savings Calculator</title>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<script type="text/javascript">
//<![CDATA[
function GasSavings()
{
  var additive     = .16;
  var litres       = document.getElementById('litres').value;
  var price        = document.getElementById('price').value;
  var savLitres    = litres * additive;
  var cost         = price * litres;
  var costFuel     = litres * (1 - additive);
  var costAdditive = costFuel / 7570 * 369;
  var costNet      = costFuel + costAdditive;
  var costSavings  = cost - costFuel - costAdditive;
  // Construct output
  document.getElementById('savlitres').innerHTML    = Money(savLitres.toFixed(0));
  document.getElementById('cost').innerHTML         = "$"+Money(cost.toFixed(0));
  document.getElementById('costfuel').innerHTML     = "$"+Money(costFuel.toFixed(0));
  document.getElementById('costadd').innerHTML      = "$"+Money(costAdditive.toFixed(0));
  document.getElementById('costnet').innerHTML      = "$"+Money(costNet.toFixed(0));
  document.getElementById('costsaveday').innerHTML  = "$"+Money((costSavings/365).toFixed(0));
  document.getElementById('costsaveyear').innerHTML = "$"+Money(costSavings.toFixed(0));
}
function Money(m)
{ // 1000000 -> 1,000,000
  m = m.toString(); var a = m.length - 3;
  while (a>0){ if(m.charAt(a)!==null){m=m.substr(0,a)+','+m.substr(a);} a-=3; }
  return m;
}
//]]>
</script>
<style type="text/css" media="all">
body {
 font-size: 100%;
}
#calc {
 margin: 0 auto;
 width: 30em;
 text-align: center;
}
#calc input {
 text-align: right;
 font-size: .8em;
}
#results {
 margin: .4em auto;
 border-collapse: collapse;
 text-align: left;
}
#results th {
 padding: .2em .4em;
 text-align: center;
 color: #fff; background: #006;
 border: 1px solid #004;
}
#results td {
 padding: .2em .4em;
 border: 1px solid #fff;
}
#result td {
 padding: .2em .4em;
 border: 1px solid #999;
}
#results td.btn {
 text-align: center;
}
#results td.col-2 {
 text-align: right;
 font-weight: bold;
}
#results tr.main td {
 color: #000; background: #ff6;
}
#result tr:hover td {
 background: #ddf;
}
</style>
</head>
<body>
<div id="calc">
<table id="results">
<thead>
<tr><th colspan="2">Gas Additive Savings Calculator</th></tr>
<tr>
  <td><label for="litres">Number of litres of diesel fuel used</label></td>
  <td class="col-2"><input type="text" id="litres" name="litres" size="7" /></td>
</tr>
<tr>
  <td><label for="price">Average price per litre of fuel</label></td>
  <td class="col-2"><input type="text" id="price" name="price" size="4" /></td>
</tr>
<tr>
  <td colspan="2" class="btn"><button onclick="GasSavings()">Calculate Savings</button></td>
</tr>
<tr><th>Description</th><th class="col-2">Amount</th></tr>
</thead>
<tbody id="result">
<tr>
  <td>Savings in litres</td>
  <td class="col-2" id="savlitres"> </td>
</tr>
<tr>
  <td>Existing Cost of Fuel for 1 year</td>
  <td class="col-2" id="cost"> </td>
</tr>
<tr>
  <td>Cost of Fuel using ACES II</td>
  <td class="col-2" id="costfuel"> </td>
</tr>
<tr>
  <td>Approx. Cost of ACES II for 1 year</td>
  <td class="col-2" id="costadd"> </td>
</tr>
<tr>
  <td>Net New Cost of fuel and ACES II for 1 year</td>
  <td class="col-2" id="costnet"> </td>
</tr>
<tr>
  <td>Savings per day:</td>
  <td class="col-2" id="costsaveday"> </td>
</tr>
<tr class="main">
  <td>Total field cost savings per year</td>
  <td class="col-2" id="costsaveyear"> </td>
</tr>
</tbody>
</table>
<!-- end calc --></div>
</body>
</html>
 
You must log in or register to reply here.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom
Back