Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    Action depending on contents of a cell

    This is so straightforward and simple that I'm embarrassed that I can't find the answer for myself!

    I want have a formula in a second cell, whose result depends on the contents of a first cell, as in (trivial example):

    IF cell A1 is blank THEN leave cell B1 blank ELSE multiply contents of cell A1 by 10 and place the result in cell B1. Please could you give me a second formula (or more complex single formula) which covers the case where cell A1 is zero (not blank) but I still want to end up with a blank cell B1.
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Action depending on contents of a cell

    if I read your post correctly, =IF(A1=0,"",A1*10) should get you started.

    Brooke

  3. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Action depending on contents of a cell

    Brooke: your formula works brilliantly (hangs head in shame!) John
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Action depending on contents of a cell

    John,

    If a cell is zero, and you want blanks displayed, just format the cell.

    Ie:
    The formula in cell B1 is "=A1*10"

    Then format cell B2:
    Format, cells, "number" tab, custom, and under type, enter :
    0;-0;
    That means if the cell is postive, show the number as a whole number; if negative, show the number with a minus sign; if zero, don't show anything.

    (that's to display integers- you can change the formatting for other cases- eg:
    0.00;-0.00;

    Brooke's example '=if(A1=0,"",A1*10)' works fine- but there's a danger that a cell using B2 for a calculation will fall over.

    If A1 was empty, as opposed to blank, then "=A1*10" would work fine in B2 anyway.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  5. #5
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Action depending on contents of a cell

    Geoff - thanks, your suggestion is good. I want to set up the (same) formula in the cells of a column, and just make the values appear in that column, working progressively downwards, whenever the cell to the immediate left is given a value, otherwise it should look as if there is nothing in the cells (IYSWIM!).
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Action depending on contents of a cell

    John,

    I realsied after I posted that there's another option.

    Under Tools, Options, under the "View" tab, there's a "zero options" box. Untick that, and you won't have any zero values showing on the sheet at all.

    That might not be an advantage if you want to show zeroes elsewhere on the sheet, but it's worth bearing in mind.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

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