Google-sheets – Help with complicated math and syntax

formulasgoogle sheets

I am trying to create a calculator for this mobile game that I play (Lord’s Mobile) but cant seem to figure out the formula.

1) In this game, you can get your upgrade time reduced when your guild members press a “help” button.

2) A single “help” reduces your current time by 1%. So if someone presses help, your time (X) = (Time-(Time*0.1)). When another person presses help, it would be Y = (X-(X*0.1)), then Z=(Y-(Y*0.1)), so on and so forth, where each new letter represents the new time.

Another example: If your total time is 24 hours (1,444 minutes):

Help 1 would be (1,444-(1,444*0.1)=1,300min left;
Help 2 would be (1,300-(1,300*0.1)=1,170min left (and so on).

3) There is a minimum (or maximum?): Once 1 minute > 1%, it reduces by 1 minute instead.

4) Castles can be levels 1-25. The amount of helps you can receive depends on your castle level. The amount increases consecutively, with Lvl 1 being 5 times, and 25 being 30 times.

With all of these factors, I can’t seem to figure out how to calculate this in my spreadsheet. Even without the additional factors, I was at least trying to figure out how to do the whole “1% Reduction” in one cell.

I was hoping some math genius might see this. And even without the math help, I’m posting this here because I don’t know the correct syntax. Anyone have a clue as to what to do? Thanks!

Best Answer

You have a few problems in your examples: 24 hours is 1440 minutes.

Also you say 1% but then your example shows that you are calculating a 10% reduction.

If you want a 10% reduction just multiply x*0.9
if it is a 1% reduction just multiply x*0.99

3) There is a minimum (or maximum?): Once 1 minute > 1%, it reduces by 1 minute instead.

you need to include an if statement. assuming the previous value is in cell b7

for a 1% reduction:

   =if(b7<100,b7-1,b7*0.99)

for a 10% reduction

   =if(b7<10,b7-1,b7*0.9)

4) Castles can be levels 1-25. The amount of helps you can receive depends on your castle level. The amount increases consecutively, with Lvl 1 being 5 times, and 25 being 30 times.

I have no idea what that means in relation to the previous requirements.

Code

/**
 * myWords(searchRange, checkRange)
 * @param {Array.Array} searchRange
 * @param {Array.Array} checkRange
 * @customfunction {int} 
 */
function myWords(searchRange, checkRange) {
  // small error handling
  if(searchRange[0].length > 1 || checkRange[0].length > 1) {
    throw "one of the ranges has more than one column";
    return;
  }  

  // counting of words
  var output = [];  
  for(var k=0, kLen=searchRange.length; k<kLen; k++) {
    var count = 0; 
    var cell = searchRange[k][0];   
    for(var i=0, iLen=checkRange.length; i<iLen; i++) {
      var spl = checkRange[i][0].split(" ");
      if(spl.length == 1) {
        if(spl == cell) {
          count++;
        }
      } else {
        for(var j=0, jLen=spl.length; j<jLen; j++) {
          if(spl[j] == cell) {
             count++;
          }
        }
      }
    }
    output.push([count]);
  }

  // return individual outcomes
  return output;
}

Screenshot

enter image description here

Note

This piece of code will also work in the new Google Spreadsheet. Paste the code in the script editor, under Tools>Script editor. Press the save button and you can start using the script, as shown in the screenshot.

Google-sheets – INDEX-formula throws random #REF!-errors due to [illogical] column value out of range

UPDATE: The #REF! errors were not returned randomly in a broad sense, they appear sometimes and once the first occurrence appear on one cell then it repeats to the cells below it.

This looks to be a latency problem inherent to GOOGLEFINANCE(), as was previously mentioned by Normal in a comment to the question.

In order to have a "rock-solid formula" instead of using GOOGLEFINANCE directly in column E use an auxiliary sheet to hold the historical currency conversion rates as fixed values instead of calculated values through a formula.

You could use GOOGLEFINANCE() to get that data but then copy and paste values only.

Short answer before the OP shared a demo spreadsheet

Instead of using ambiguous date format (00/00/0000) use a explicit one like

dd/mmm/yyyy
mmm/dd/yyyy

among others

Explanation

The problem could be due to the regional settings / date format of your spreadsheet / data.

I just tried to reproduce the problem but it works fine in my environment:

Date format: dd/mm/yyyy
Regional settings: Mexico
Account language: Spanish Latin America
Always show the function names in English

Reference

GOOGLEFINANCE - Google Docs editors Help