+ Reply to Thread
Results 1 to 16 of 16

IF function and multiplication of other cells with"0"

  1. #1
    Registered User
    Join Date
    04-17-2012
    Location
    Germany
    MS-Off Ver
    Excel 2003
    Posts
    8

    IF function and multiplication of other cells with"0"

    Hi guys, I hope you can help me. I want to achieve something in Excel but I don't know if it's even possible.
    I created a dropdownmenu and I want an if-function that checks if option 1 from the dropdownmenu is present and if it is, a number of cells in my table shall be set to 0 or multiplicated with 0. They should always become 0 when option 1 of the dropdownmenu is chosen.
    The thing is, I can't (or at least I don't know how) write the formula in the before mentioned cells, cause these cells also are "input-cells". i.e. in case option 2 of the droptdown menu is chosen, specific values have to be put into these cells. In that case the formula should either do nothing or multiplicate those values with 1, as to not change them.
    Since I'm not really proficient with formulas in Excel I created something like this, in a seperate cell outside of the dropdownmenu and outside the mentioned "input-cells":
    cell containing formula: B1

    =IF(A1="Basic";B10="(B10*0)";B10="(B10*1)") --> example for only one cell; usually I'd encase A and B in $'s

    Ok this obviously doesn't work. I guess I need some kind of "if option 1 is true, do this at that location" like thing.

    So is there a way to make that possible?

    cheers

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: IF function and multiplication of other cells with"0"

    Hi Sam,

    Welcome to the forum.

    =if(a1="basic", b10*0,b10*1)

    Apply the above formula and let me know if this is what you were looking for else upload as sample workbook. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: IF function and multiplication of other cells with"0"

    Hello,

    What I gather from your post is that you just want a simple IF statement but aren't sure how to use it? Is that correct? If so, below is an example based on your example above.

    =IF(A1="Basic",0,formula if false)

    where 0 is the value that shows in the cell if A1 is equal to the word basic. If the the value in A1 is not Basic then you would have the formula or value you would like to appear in that cell where I have placed the words formula if false.

  4. #4
    Registered User
    Join Date
    04-17-2012
    Location
    Germany
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: IF function and multiplication of other cells with"0"

    OKay, first of all thanks for those really fast replys!
    The Table:
    A1: a dropdownmenu with 2 options, either "basic" or "advanced"
    B10: a cell where I have to enter a value*
    @dilipandey: Your formula kind of works, I wrote it in cell B1 and it copied current value of B10 into itself and either set it to 0 or didn't change it, dependend on A1 option. (edited this and yeah I confused our names Oo ^^)
    @rvasquez: What would be the correct syntax for the "formula if false" part? Cause it didn't work when I entered these words in the "else_part".


    The formula can't be entered into B10 directly, can it? Cause when I enter a new value manually (cause A1 isn't basic) it would delete the formula, wouldn't it?
    I kind of need the formula outside of cell A1 and outside of cell B10, that checks the condition in A1 and depending if false or true does one or another thing to cell B10.

    cheers

    *simplified, cause I have a lot more than one cell that needs to be "treated" ;]
    Last edited by SamKnows; 04-17-2012 at 02:21 PM.

  5. #5
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: IF function and multiplication of other cells with"0"

    Hi,

    @Sam: Your formula kind of works, I wrote it in cell B1 and it copied current value of B10 into itself and either set it to 0 or didn't change it, dependend on A1 option.
    was this for me i.e., DILIPandey?

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  6. #6
    Registered User
    Join Date
    04-17-2012
    Location
    Germany
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: IF function and multiplication of other cells with"0"

    Ow, my mistake O.O, sry.

  7. #7
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: IF function and multiplication of other cells with"0"

    Ok.. np.

    Would you be able to upload a sample workbook?

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  8. #8
    Registered User
    Join Date
    04-17-2012
    Location
    Germany
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: IF function and multiplication of other cells with"0"

    Ok, I quickly made a nice example .
    Can I upload it directly here in the forum somehow or do I need a filehoster? Never used one yet -.-

    Cheers

    Edit: found the advanced option ;]
    Attached Files Attached Files
    Last edited by SamKnows; 04-17-2012 at 03:08 PM.

  9. #9
    Registered User
    Join Date
    04-17-2012
    Location
    Germany
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: IF function and multiplication of other cells with"0"

    Still need a function that operates not in her own but in another cell.*

    Cheers

    *bump

  10. #10
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: IF function and multiplication of other cells with"0"

    Hi "SamKnows"

    Ok.. I have got this and applied the formula which will change the value to 0 if A1 is basic. And if A1 is Advanced it will pick up the manual entries (which user will enter in column C)

    =IF($A$1="basic",0,C7)


    ExampleMap(1).xls

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  11. #11
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: IF function and multiplication of other cells with"0"

    If I follow correctly, what you need cannot be achieved with a formula. You need Macros. You want B10 to change to 0 if you select Basic from A1, or remain unchanged if "Advanced" is selected from A1.

    That can easily be done with a ChangeEvent code on A1, but you need to give us more info, do you want all values on column B to change or just B10? Will it be B10 all the time or will the cell you want to change appear on difference cells on different scenarios?

    If you don't want macros, as these have to be enabled everytime by the user or any other reason, you will have to be happy with using a formula on C7:C15 to do the same calculation you're doing in B1. Enter the formula in C7:
    Please Login or Register  to view this content.
    And drag down as needed. If further down into whatever you want to do you will have to refer to column C and not column B in your formulas.
    Ron
    Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad

    Kindly

    [1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
    [2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
    [3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated

  12. #12
    Registered User
    Join Date
    04-17-2012
    Location
    Germany
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: IF function and multiplication of other cells with"0"

    @ron2k_1

    If I follow correctly, what you need cannot be achieved with a formula. You need Macros. You want B10 to change to 0 if you select Basic from A1, or remain unchanged if "Advanced" is selected from A1.
    Thats exactly what I need and also for all cells B7 till B15.

    That can easily be done with a ChangeEvent code on A1, but you need to give us more info, do you want all values on column B to change or just B10?
    In this examplemap it would be for all values on column B (B7 - B15) but if there were more values, lets say in another table beneath the first one (Electricity) starting from B20, those values on column B (f.i. B20 - B30) shouldn't be changed at all. Only B7:B15.

    Will it be B10 all the time or will the cell you want to change appear on difference cells on different scenarios?
    I don't know if I correctly understood this question. It'll be always those 9 cells on the B column. Is it dangerous, complicated or tedius for the user to allow makros in the end?

    @dilipandey: Your solution works ^^ so you have my thanks for it, it makes the whole table a lot bigger though. So I'd do it that way if there is no other possibility.

    Cheers

  13. #13
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: IF function and multiplication of other cells with"0"

    @dilipandey: Your solution works ^^
    That's great.. either it would be this way or VBA (macros)

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  14. #14
    Registered User
    Join Date
    04-17-2012
    Location
    Germany
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: IF function and multiplication of other cells with"0"

    I once, a long time ago read a Beginner's Guide to C++, that was the only time I actually tried programming. Luckyly that seems to help me understanding the Introduction to VBA programming I'm reading now :]. I hope I have enough endurance and I'm clever enough to get through that ^^.

    Cheers

    Edit: I wrote a little VBA programm with the excel integrated editor:

    Please Login or Register  to view this content.
    How can I get this to be automatically activated everytime I change cell A1 from Advanced to Basic? Right now I have to go into the VBA editor and press the "play button" but thats kind of complicated.

    * Changed the name of the first sheet of ExampleMap to calculation
    Last edited by SamKnows; 04-19-2012 at 10:30 AM.

  15. #15
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: IF function and multiplication of other cells with"0"

    well, you can use the following. Just bear the following in mind: Once you've selected Basic, all values in B7:B15 will change to 0, and you cannot get back the values you had there before selecting Basic (even if you select Advanced after selecting Basic by mistake). If you still want to retain the old values (before they are zeroed out) you need to copy the old table to somewhere else, you can instruct the code to that for you.

    Right click on tab "Tabelle1" Select "View Code" and paste the following.
    Please Login or Register  to view this content.
    If you want to copy the old values to column C before zeroing column B, then you can use this:
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    04-17-2012
    Location
    Germany
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: IF function and multiplication of other cells with"0"

    Thanks a lot !

    I don't understand everything of this code, but it works. I tried to make it so, that it stores the previous values in B7:B15 in the sourcesheet, to be able to redo setting all cells B7:B15 to 0.
    I added an ElseIf function and copied the first part of your code into it, that should reverse everything. As it is now it pretty much works the only strange thing happening is that I get the values from B7:B15 not only in sourcesheet.A7:A15 but also in sourcesheet.B7:B15?
    Is there a mistake somewhere or a better approach to achieve this? Maybe with another condition that it only returns sourcesheet.A7:A15 values if those cells aren't 0 or empty.

    Please Login or Register  to view this content.
    Cheers and many thanks!
    Last edited by SamKnows; 04-19-2012 at 01:43 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1