Moving a formula from Numbers to Sheets

Question

Level 1

4 points

Moving a formula from Numbers to Sheets

Hi everyone, I hope someone can help.

I have a calculator on Numbers that works perfectly on my iPad and iPhone, but when I try and migrate it to Google Sheet, the formulas are not recognised.

The calculator essentially allows you to put in how many sales you make and what % of KPI you get on that,

I.e. Let’s say the target is 50% attach rate

You would put in 10 sales with 40% attach rate, based on another cell that shows 4 (10 x 0.4) this would then work out how many more sales in a row you would need to achieve the 50%, so would then show 2. The formula is below.

IF(ROUNDUP(((LEFT(G8,2)÷100×$B4)−G4)÷(1−(LEFT(G8,2)÷100)),0)<1,"",ROUNDUP(((LEFT(G8,2)÷100×$B4)−G4)÷(1−(LEFT(G8,2)÷100)),0))

This however returns an error when used in Google Sheets, however I need this to link to another Google Sheet thus need to find a way to migrate it.

There is a link to the calculator on Numbers below for anyone wanting to try it out.

https://www.icloud.com/numbers/0fubTPtazAHliO-SnlBp2H5wg#Calc

The 4 white cells at the top are the ones you edit and the numbers next to the % shows how many more in a row you need to achieve said result.

I hope this makes sense, if not just ask.

All I want is a Google Sheets equivalent of this.

Thanks.

iPad Pro, iPadOS 13

Posted on Oct 26, 2020 10:35 AM

Reply

Answer 1

Best reply

Halliday

Level 4

2,671 points

Oct 26, 2020 10:03 PM in response to - asw091 -

Yes, - asw091 -. You get a parse error because Google Sheets doesn’t understand the following:

The multiplication symbol: needs to be the asterisk (*)
The division symbol: needs to be the slash (/)
The subtraction symbol: needs to be the hyphen (-)

I only figured out the last by noticing numbers and cell references that were not being properly recognized.

Other than those three characters, the formula works in Google Sheets.

Note: isn’t there an option to not have Numbers replace the normal keyboard characters?

If that’s so, one should be able to use that to help port such formulae (formulas).

Reply

Answer 2

Barry

Level 9

59,885 points

Oct 26, 2020 12:42 PM in response to - asw091 -

What error message do you get from Google sheets?

Is the formula posted taken from Numbers, or is that the way it is displayed in GS?

One potential source is LEFT(G8,2)÷100×$B4

GS may not recognize ÷ as the division operator or × as the multiplication operator.

Try replacing these with / and * respectively in the GS version.

Also, LEFT(G8,2) returns a text value consisting of the first two digits of the (presumed) number value in G8.

GS may want an actual number value here, and to get that, you may have to use VALUE(LEFT(G8,8)).

(All guesswork, as I'm not familiar with google sheets.)

Regards,

Barry

Reply

Answer 3

- asw091 - Author

Level 1

4 points

Oct 26, 2020 4:30 PM in response to Barry

Hi Barry,

Firstly, thank you for taking the time to respond.

The error message I get is ‘Formula parse error.’ I can confirm all cell are present and in the correct place for the formula.

The formula posted is taken form numbers.

I have replaced the ÷ and x with / and * which appears to have triggered the formula in some way by now colour coding the cells in the formula whereas before it was unrecognised as you had presumed and just all black text.

I have tried adding in VALUE(LEFT(G8,2)) however nothing appears to have changed, it still returns the ‘Formula parse error.’

This is the Google Sheet once imported. ( Feel free to edit, I have copies).

https://docs.google.com/spreadsheets/d/1mNNQP-dUTbbVlwUhcVe2C4lm-YTBKQqKUDlbmB354JQ/edit

Reply

Answer 4

Barry

Level 9

59,885 points

Oct 26, 2020 9:38 PM in response to - asw091 -

Took a look. You may want to replace this copy as I made some changes and lost track of here I was, so neither returned the table to original condition nor carried through to the end.

Some notes:

Missed the ability to copy a formula then paste it into an expandable text box where the whole thing could be viewed.

Not sure why you are using LEFT(G8,2) as G8 contains 70%. In numbers, this would be the numeric value 0.70, formatted as percentage.

In the same formula, you are dividing that value by 100 then multiplying the result by B4.(which contains the value 20 (or 20%) Ignoring the B4 part, if the value in G8 is a numeric value, it is already 'divided by 100' related to the percentage display—as noted above, 70% = 0.70.

Haven't found a cause for a parsing error yrt, though.

Regards,

Barry

Reply

Answer 5

Barry

Level 9

59,885 points

Oct 26, 2020 11:10 PM in response to Halliday

Hi Halliday,

Aha! Hadn't caught the minus sign substitution!

Agreed that these should be converted to the old operator symbols when exporting to a format that doesn't recognize the standard multiplication, division and subtraction signs as 'the corresponding math 'operators'.

Within Numbers, I'm becoming used to them (though, as mentioned), I hadn't noticed the subtract sign/hyphen swap.

The original character assignments to these operations was done when computer keyboards were modelled on the teletype keyboard and the 256 character ASCII set.

The new dress is a child of the smart phone/virtual onscreen keypad era with it's much larger character set, which includes the 'real' signs for the math operations, reducing (removing?) the need to rely on the typewriter/teletype substitutes.

Eventually, the world will catch up. Meantime I agree, there is a need to make the reverse substitution a part of conversion to formats that haven't updated.

Regards,

Barry

Reply