Quickoffice formula help!

Discussion in 'iPad Apps' started by Gabriel1, Feb 1, 2012.

  1. Gabriel1
    Offline

    Gabriel1 Super Moderator Staff Member

    Joined:
    Mar 11, 2011
    Messages:
    17,132
    Thanks Received:
    747
    Trophy Points:
    0
    Location:
    Cranbrook, Kent, UK
    Ratings:
    +749 / 0
    Hi guys, I'm trying to set up a new spreadsheet and I'm doing something wrong but my brain is just not comprehending it at the moment.

    Here is the problem, in the first image I am calculating a total of one cell minus another cell, this works fine. In the second image I m trying to calculate the previous answer multiplied by another cell......but it isn't working and despite all my efforts I can't get it to work.......your help would be most welcome.

    image-13920750.png



    image-1072198001.png

    The Archangel
    Last edited: Feb 1, 2012
  2. psy
    Offline

    psy iPF Novice

    Joined:
    Dec 29, 2011
    Messages:
    37
    Thanks Received:
    1
    Trophy Points:
    0
    Ratings:
    +1 / 0
    I have created spreadsheets with sums using quick office pro, I used brackets so the sum in the bracket was calculated before the next bit.... Your situation sounds more complex but I did find the help files on their web site very help full.
  3. hpulmer
    Offline

    hpulmer iPad Junkie

    Joined:
    Aug 30, 2011
    Messages:
    730
    Thanks Received:
    31
    Trophy Points:
    28
    Location:
    Germany
    Ratings:
    +31 / 0
    Gabriel1,
    Nothing wrong with your formulas, it's all about the format!
    See below, I used your formulas and there is no problem - just in the £/miles you put a "." as the decimal separator, I put the "," - do NOT ask me why this is not depending on the country settings in Quickoffice (or it might well be) - I am using the German setting on my iPad.....

    Attached Files:

  4. psy
    Offline

    psy iPF Novice

    Joined:
    Dec 29, 2011
    Messages:
    37
    Thanks Received:
    1
    Trophy Points:
    0
    Ratings:
    +1 / 0
    I just recreated your sums in my quick office pro.
    Try removing sum and brackets..... So. You have. =b3-a3. And. =c3*d3

    Give that a go.....worked on mine. :)
  5. Gabriel1
    Offline

    Gabriel1 Super Moderator Staff Member

    Joined:
    Mar 11, 2011
    Messages:
    17,132
    Thanks Received:
    747
    Trophy Points:
    0
    Location:
    Cranbrook, Kent, UK
    Ratings:
    +749 / 0
    Thanks guys, I really appreciate the help. psy's solution worked so I am a happy bunny again and can keep my mileage updated!

    The Archangel
  6. hpulmer
    Offline

    hpulmer iPad Junkie

    Joined:
    Aug 30, 2011
    Messages:
    730
    Thanks Received:
    31
    Trophy Points:
    28
    Location:
    Germany
    Ratings:
    +31 / 0
    Justtrieditout - works fine with sum and brackets, but not with the . instead of the , on my sheet...
  7. hpulmer
    Offline

    hpulmer iPad Junkie

    Joined:
    Aug 30, 2011
    Messages:
    730
    Thanks Received:
    31
    Trophy Points:
    28
    Location:
    Germany
    Ratings:
    +31 / 0
    Oops... But not with sum and brackets on a 'product' !
    ;o)
  8. Gabriel1
    Offline

    Gabriel1 Super Moderator Staff Member

    Joined:
    Mar 11, 2011
    Messages:
    17,132
    Thanks Received:
    747
    Trophy Points:
    0
    Location:
    Cranbrook, Kent, UK
    Ratings:
    +749 / 0
    Maybe I need to change the formatting somewhere but at least it's working. I'll do a copy/paste of the whole sheet and play around with the formatting at the weekend.....not enough time to play during the week at the moment.

    The Archangel
  9. psy
    Offline

    psy iPF Novice

    Joined:
    Dec 29, 2011
    Messages:
    37
    Thanks Received:
    1
    Trophy Points:
    0
    Ratings:
    +1 / 0
    Sweet.
  10. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,474
    Thanks Received:
    2,057
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,372 / 1
    A probably unnecessary explanation.

    The SUM function adds up all the cells in a range. You don't put an arithmetic formula inside it; just the first and last cell in a range that you want to be added up. Say you wanted to add up cells A1, A2, and A3. You'd enter SUM(A1,A3) and you'd get the sum of all three cells. {Cell separators may differ from app to app, it might be SUM(A1:A3), I don't remember what Excel's separator was.}

    You just wanted the difference and multiplication of two cells. The only functions you needed were the math operations inbetween, the (-) minus and (*) multiply operations.
    Last edited: Feb 1, 2012
  11. Gabriel1
    Offline

    Gabriel1 Super Moderator Staff Member

    Joined:
    Mar 11, 2011
    Messages:
    17,132
    Thanks Received:
    747
    Trophy Points:
    0
    Location:
    Cranbrook, Kent, UK
    Ratings:
    +749 / 0
    I used to be a whiz on excel formulas but I seem to have had all my prior knowledge drained into my feet.......you are right, I didn't need to use the SUM function at all! I need to spend some time re-learning everything that I have forgotten!

    The Archangel
  12. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,474
    Thanks Received:
    2,057
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,372 / 1
    I have to sympathize there. These days I often feel I spend more time relearning old skills than acquiring new ones. ;)

    Fortunately, relearning is much easier than starting cold.
  13. AyePad
    Offline

    AyePad iPF Noob

    Joined:
    May 4, 2012
    Messages:
    5
    Thanks Received:
    0
    Trophy Points:
    0
    Location:
    Inverness, Scotland
    Ratings:
    +0 / 0
    Hi all,
    I am trying to add up the values in a column in Quick Office Pro. One of the values is a negative number which I don't want to include in the total. In MS Excel I would probably just use SUMIF(A1:A100,>0). This would add up all cells in the range A1 to A100 which are greater than zero. Simple!

    However, I can't find the SUMIF function in Quick Office and although it does have various other functions I can't quite work out which one to use. Each function does give you the format for the formula but not a detailed explanation or even some examples. I have tried browsing Quick Office Help but to no avail.

    Does anyone know how to add up positive numbers ONLY or find a negative number in order to subtract it from the total?
    Any suggestions on how to achieve this or other places to search would be greatly appreciated!

    Och Aye the Noob!
  14. AyePad
    Offline

    AyePad iPF Noob

    Joined:
    May 4, 2012
    Messages:
    5
    Thanks Received:
    0
    Trophy Points:
    0
    Location:
    Inverness, Scotland
    Ratings:
    +0 / 0
    I solved my problem by using the IF Function.

    My negative number was being generated by subtracting A1 from A2 when A2 had not had a value entered yet. So my formula in B2 was =A2-A1. Where A1 had a value of 125 and A2 was empty the value in B2 was -125.

    I used the IF function to check if the value of A2 was empty or equal to zero and if so make B2 equal to zero otherwise carry out the subtraction as normal. So the new formula in B2 is now =IF(A2=0,0,A2-A1).

    Now if I use the SUM function in Column B the answer is a positive value!

    Simple stuff really but much easier if you can use the SUMIF Function to ignore negative numbers.

    Oh Aye the Noob!!!!!
  15. borryman
    Offline

    borryman iPF Noob

    Joined:
    Jun 18, 2012
    Messages:
    5
    Thanks Received:
    0
    Trophy Points:
    0
    Location:
    UK
    Ratings:
    +0 / 0
    I reckon you should enter thus: =Your previous answer * the other cell. Example: =C3*D3
  16. AyePad
    Offline

    AyePad iPF Noob

    Joined:
    May 4, 2012
    Messages:
    5
    Thanks Received:
    0
    Trophy Points:
    0
    Location:
    Inverness, Scotland
    Ratings:
    +0 / 0
    Hi borryman,
    Thanks for your suggestion.

    If I multiply the previous cell by the next cell (which is empty) I do get a zero which solves the problem with negative numbers but as soon as I input my new value into the next empty cell it multiplies it with the previous cell and doesn't give me the answer I am looking for.

    I want to subtract the previous cell from the newly input cell and show the difference in the next column. Then I want to total the "difference" column.

    I can't see how multiplying can work in this situation.

    Thanks

    AyePad
  17. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,474
    Thanks Received:
    2,057
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,372 / 1
    You should use an IFELSE statements. I don't use QuickOffice, so I can't offer specific instuctions; but it should go something like this.

    IFELSE (x-y < 0) then y-x, else x-y

    It tests to set if your result (x-y) is negative (less than zero). If it is, then it reverses the equation (y-x) so that you get a positive difference. If it is not less than zero you get the result of x-y, which will be positive or zero.
  18. AyePad
    Offline

    AyePad iPF Noob

    Joined:
    May 4, 2012
    Messages:
    5
    Thanks Received:
    0
    Trophy Points:
    0
    Location:
    Inverness, Scotland
    Ratings:
    +0 / 0
    Hi twerppoet,

    QuickOffice seems to have a preset list of Functions. I can't find "IFELSE" on the list but "IF" is so if I drop the "ELSE" and use your statement it works fine. I've tried manually typing in various statements but they always result in a "Function Not Recognised" message appearing. If it's not on their list it just won't work.

    Thanks for your help.

    AyePad
  19. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,474
    Thanks Received:
    2,057
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,372 / 1
    I must have been operating off of my often faulty memory. Number doesn't have an IFELSE statement either. It just has the IF statement which includes an optional else clause. Sorry for the confusion. Other than that the explanation of how to use it is the same.

    By the way, the Numbers expression does not look like that either. The example I used was meant to be readable, not usable. The Numbers example would look more like this.

    NumberIFexample.jpg

    You can't just type in formulas in iOS Numbers, so a text representation can be misleading. Always read and use the example formats in the app you are using.

    Again, I did not mean to confuse the issue, the opposite actually. :eek:

Share This Page

Search tags for this page
copy rumus di quickoffice
,
decima commma in quick office
,

division format in quickoffice spreadsheet

,
formula in excel quick office
,
formula of sum for quick office table
,

formula to subtract in quickoffice excel

,

formulas quickoffice

,
funcao if quickoffice
,

how to calculate the product in quickoffice

,
how to do formula on quick office
,

how to get sum in excel quickoffice app

,
quick office formulae list
,
quick office spreadsheet date
,

quickoffice formulas

,

quickoffice ipad sum function

,

quickoffice iphone sum

,
quickoffice spreadsheet dates
,
quickoffice spreadsheet formula for subtraction
,

quickoffice subtract

,
quickoffice sum