Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Having the option of which input will effect calculations

    The attached spreadsheet shows that there are calculated cells in Column K, P & V. The input columns are Q & W. I would like to either input the whole number in the respective cell in column Q or the % number in the respective cell in column W. How am I able to accomplish this on the worksheet?

    Thank you.
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    To keep things simple and easy to debug, I would add a helper column with a formula something like this (for row 6 as an example):

    Code:
    =IF(AND(Q6<>"",W6<>""),"Error: enter data in one place only",IF(AND(Q6="",W6=""),"No data",IF(Q6<>"",Q6,W6)))
    Play around with this to get the exact outcome you are looking for, and then use the helper column as the input for your subsequent calculation.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    This works just fine. Many Thanks

  4. #4
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Fukuoka,

    Again thanks for the assistance. I was looking at the formula and wondering exactly how it works. Could you please break it down into sections and explain it.


    =IF(AND(Q6<>"",W6<>""),"Error: enter data in one place only",IF(AND(Q6="",W6=""),"No data",IF(Q6<>"",Q6,W6)))

    Thanks again

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    An IF function has three parts:

    1. The test you are applying
    2. What to display if the test is TRUE
    3. What to display if the test is FALSE

    The example I gave uses nested IFs, which I hope you can follow in this explanation . . . .

    The first test applied is looking to see if there is data in BOTH Q and W: this is written as
    AND(Q6<>"",W6<>""). If both Q6 isn't blank [Q6<>""] and W6 isn't blank [W6<>""], then the test is TRUE, and the cell will display "Error: enter data in one place only".

    But if they are not both blank, ie the test is FALSE, go on to another IF statement where the test is whether both W6 and Q6 are blank, written as
    AND(Q6="",W6="").

    If they are both blank then the test is TRUE and
    "No data" is displayed.

    But if one of them is not blank the test is FALSE and we go on to the third nested IF test. This last one asks whether Q6 isn't blank: [
    Q6<>""]. If that's TRUE then display Q6. If its FALSE then it must be W6 that isn't blank, so that is displayed.

    Hope this helps.

    PS Fukuoka is the city where I am !
    Last edited by MartinM; 2012-06-22 at 04:05.

  6. #6
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    THANKS but how does the IF(AND) work together.

    and sorry about the name vs city.. by the way where is Fukuoka located

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    MNN,

    The And() allows you to do multiple tests, all of which must be true for the IF to be true.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Fukuoka is at the West end of Japan, quite close to South Korea.

  9. #9
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you both.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •