+ Reply to Thread
Results 1 to 5 of 5

Please help beginner with a formula

  1. #1
    Registered User
    Join Date
    08-28-2021
    Location
    london
    MS-Off Ver
    xp , 2000
    Posts
    9

    Please help beginner with a formula

    Hello !
    Please help me with the following problem !!!

    In the A column there are only 1s or 0s
    In the B column there can be any positive or negative number.

    I need a formula in the C column , that will give me back a ''1'' only when there is both a 1 in the A column and a positive number in the B column. If any of these conditions are not met , I should get back a ''0'' in the C column.

    I attached a example excel file.
    Thank you very much !
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,791

    Re: Please help beginner with a formula

    =if(and(a2=1,b2>0),1,0)
    Attached Files Attached Files
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Valued Forum Contributor
    Join Date
    10-29-2012
    Location
    Mojokerto,Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    543

    Re: Please help beginner with a formula

    =n(a1*b1>0)

  4. #4
    Registered User
    Join Date
    08-28-2021
    Location
    london
    MS-Off Ver
    xp , 2000
    Posts
    9

    Re: Please help beginner with a formula

    Thank you very much etaf, your solution works very well !
    However a different problem arose for me.
    Is it possible instead of having 5 results for the 5 examples to only have 1 result for the 5 examples ?
    For instance , if any of the 5 examples is ''correct'' as defined by my previous conditions (A=1,B=positive) , to have a ''1'' appear in a single cell (let`s say G1) If none of the 5 examples is correct to have a ''0'' appear in the same cell.
    Last edited by pyiujkui; 10-13-2021 at 07:03 PM. Reason: correction

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,791

    Re: Please help beginner with a formula

    using the N idea from Ghozi Alkatiri , never used before
    try

    =N(COUNTIFS(D10:D14,1,E10:E14,">"&0)>0)

    OR with a standard IF

    =IF(COUNTIFS(D10:D14,1,E10:E14,">"&0)>0,1,0)
    from your spreadsheet
    or using A & B columns
    =IF(COUNTIFS(A1:A10,1,B1:B10,">"&0)>0,1,0)

    Change the range to much your actual data
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Beginner Help: Formula Across 2 Sheets
    By SunRa67 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 02-03-2021, 07:27 AM
  2. Beginner needs help with formula in excel
    By CGSORS in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-02-2014, 10:40 AM
  3. Formula required for beginner
    By ptkdave in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-31-2014, 04:25 PM
  4. [SOLVED] Excel beginner formula help needed
    By halgraham in forum Excel General
    Replies: 1
    Last Post: 09-06-2013, 03:36 PM
  5. Replies: 10
    Last Post: 12-29-2011, 01:23 PM
  6. basic formula for an excel beginner
    By Fllyfishy in forum Excel General
    Replies: 4
    Last Post: 11-12-2011, 03:55 PM
  7. Help with percent formula beginner
    By Ted in forum Excel General
    Replies: 6
    Last Post: 12-01-2005, 01:55 PM

Tags for this Thread

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