Second question regarding the conversion of an Excel spreadsheet formula into JavaScript code

Hey everyone, I'm back again with another challenge. This time, I'm struggling to accurately translate an Excel Spreadsheet formula to JavaScript.

Here is the Excel formula:

 =IF(IF($B$7=TRUE,$B$28/$B$10,$B$28/$B$5)>1,1,IF($B$7=TRUE,$B$28/$B$10,$B$28/$B$5))

 WHERE
 B7  = TRUE
 B28 = 76800
 B10 = 892015
 B5  = 999500

And here is my current JavaScript attempt:

 function percent(x) { return Math.round((x-0)*100) + '%'; }

 if($('#section179').is(':checked'))
 {
  var percentRepaid = $("#rev3DScanYear").val() / $("#section179Real").val();

  if (percentRepaid > 1)
  {   
    $("#paymentCashPer").val('100.00');
  }else
  {
    percentRepaid = $("#rev3DScanYear").val() / $("#SalePrice").val();
    $("#paymentCashPer").val(percent(percentRepaid));
  }
}else
{
 //to be done   
}

 WHERE
 rev3DScanYear  = 76800
 SalePrice      = 999500
 section179Real = 892015

In my JavaScript code, I am getting a result of 8%, whereas the expected result should be 8.61% as shown in the spreadsheet.

Any help would be greatly appreciated! :o)

David

javascriptexcelexcel-2007excel-formula

Answer №1

Applying the Math.round function to ((x-0)*100)
converts x into an integer.

If you want x = 8.609720... to become x=861 and then divide by 100 to get x=8.61, you can try using Math.round(((x-0)*100)*100)/100. This method is suggested on a website here.

Why exactly are you subtracting 0 from x remains unclear.

Answer №2

Upon reviewing this once more, it seems I may have overlooked certain details during my initial analysis.

To clarify the logic:

If Section179 is selected, then Divisor equals Section179Real; otherwise, it equals SalePrice.

Calculate the smallest value between 1.00 and (rev3DScanYear / Divisor).

If my interpretation is accurate, you can perform this operation in Excel using 

=MIN(1,$B28/IF($B$7=TRUE,$B$10,$B$5))
(with the same cell references). Consequently, the following code snippet should achieve the desired outcome:

var Divisor = $("#SalePrice");

if($('#section179').is(':checked'))
{
  Divisor = $("#section179Real");
}

$("#paymentCashPer").val(Math.round(100*(Math.min(1, $("#rev3DScanYear")/Divisor)*100)/100);

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

How can I display a modal exclusively on a specific screen size?

Having a situation where two components are involved. The first component has an ng-click function that triggers a modal containing the second component. Currently, everything is working smoothly. However, the issue arises when the modal should only open a ...

javascripthtmlcssangularjs

A guide to fetching a JSON Object from a URL using Node.js and Express

As a newcomer to Node.js and Express, I am venturing into my first real project with these technologies. In this simple Node.js/Express project, my goal is to retrieve a JSON object from a URL. Additionally, I plan to create another URL that shows HTML co ...

javascriptjsonnode.jsexpresspug

Struggling with loading react storybook

I am having trouble getting my storybook app to start. It is stuck in a loading state with no errors showing in the console. Can anyone help me figure out what I am doing wrong? Check out the screenshot here storybook/main.js const path = require(' ...

javascriptreactjsstorybook

Comprehending the process of sending a variable to Routes within Node.js

Hey there! I have a MongoDB functions file where I need to pass a value generated by a function to the routes file, but I'm having trouble figuring out how to do that. The file is called labels.js const findLabels = function(db, callback) { // Acc ...

javascriptnode.jsmongodb

Sharing data between ReactJS components: Passing variables between components

I am currently working on a ReactJS project and I have the need to transmit a variable from one component to another. class Layout extends React.Component{ constructor(props) { super(props); this.state = {keyword: &apos ...

javascriptreactjs

troubles with redirecting using jQuery

I'm encountering difficulties with redirecting a page using jQuery. I have a variable called url that holds the value localhost/abc#123. However, when I use document.location.href = url;, the page redirects to just localhost/abc without including #123 ...

javascriptjquery

Can custom directives in Vue be used to define data variables?

I am using two components: SceneList and SceneCard. In the SceneList component, I am randomly setting the background color of each SceneCard and trying to pass the color code to the SceneCard component. However, I am encountering an error message: "Error i ...

javascriptvue.jsvuejs2

What is the best way to create a JavaScript "input" field that only accepts vowel letters?

Is there a way to create an "input" field that only accepts vowel letters? Additionally, I want to ensure that copying and dragging text into the input field will also be filtered out. <div class="field"> <input class="fie ...

javascripthtmlinputtext

What is the best choice for UI design framework when creating an ERP web application?

I am in the process of creating a web-based ERP application using Angular Material. However, I've noticed that each input element takes up a significant amount of vertical space on the page. This means if I have 15 input elements, I have to scroll dow ...

javascriptangularjsangular-material

What are the advantages of retrieving a complete category tree, including all categories and sub-categories, versus retrieving only the necessary branches as required?

My query is not centered around terminology accuracy, but rather on the goal of presenting a tiered structure consisting of categories, sub-categories, and sub-subcategories. In total, there are approximately 100 different categories and their respective ...

javascriptreactjsfetchdataflowfetching-strategy

Emphasizing specific lines using an array

There is a block of text containing multiple lines, each wrapped within a span with an incremented value at the end (like line-1, line-2, line-3, and so on) to distinguish between them. <div id="textbody"> <span id="line-1">what is love< ...

javascriptjquery

Executing two AJAX requests simultaneously with Django, AJAX, and jQuery in a single event

I believe I'm on the right track to getting this to work, but I could really use some assistance with the JQuery aspect. It seems that everything functions as expected after the second click onwards, but there is an issue with the functionality on the ...

javascriptjqueryajaxdjango

Attempting to retrieve information from JSON or JSONP through the utilization of the WOT API

Recently, I utilized the WOT (web of trust) API and encountered a response structured like this: process( { "www.google.com": { "target": "google.com", "0": [ 95, 84 ], "1": [ 95, 84 ], "2": [ 95, 84 ], "4" ...

javajavascriptandroidjsonjsonp

Is there a method for configuring NextAuth to utilize the /src/app directory instead?

Is there a specific method to instruct NextAuth to redirect to this particular directory, but within the src/app directory? All of my routes are now located there due to the changes in Next.js 13. Below is an example of the code I am using: export const a ...

javascriptreactjsnext.jsnext-auth

Tips for automatically closing a dropdown menu when it loses focus

I'm having some trouble with my Tailwind CSS and jQuery setup. After trying a few different things, I can't seem to get it working quite right. In the code below, you'll see that I have placed a focusout event on the outer div containing th ...

javascriptjquerycsstailwind-cssdropdown

When a cross-domain iframe is loaded in a private browser, it automatically logs the user out

I've been collaborating with a client to create a unique standalone website on a subdomain. They have requested that I utilize their API to initiate a straightforward post request when a user clicks a button. However, the client prefers the user to ut ...

javascripthtmljqueryiframe

Struggling to implement my reusable React component within a React Bootstrap modal

I have developed a reusable textarea React component: import React from 'react'; import '../form-input/form-input.styles.scss'; const TextAreaComponent = ({ handleChange, label, ...otherProps }) => ( <div className="group"> ...

javascriptreactjsecmascript-6react-bootstrapreact-bootstrap4-modal

Leverage autoplay feature in HTML5 audio to start streaming your favorite radio station

I am currently facing an issue with trying to automatically launch a webradio on my HTML file. Despite using autoplay, the radio fails to load data in time causing the autoplay feature to be blocked. To tackle this problem, I implemented a setTimeout funct ...

javascripthtmlhtml5-audiointernet-radio

Using php variable to pass data to jquery and dynamically populate content in jquery dialog

I am facing challenges trying to dynamically display MySQL/PHP query data in a jQuery dialog. Essentially, I have an HTML table with MySQL results. Each table row has an icon next to its result inside an anchor tag with the corresponding MySQL ID. echo &a ...

javascriptphpjqueryhtmlmysql

The importance of dependencies in functions and testing with Jasmine

In troubleshooting my AngularJS Service and Jasmine test, I encountered an issue. I am using service dependency within another service, and when attempting to perform a Unit Test, an error is thrown: TypeError: undefined is not an object (evaluating sso ...

javascriptangularjsunit-testingjasminekarma-jasmine