+ Reply to Thread
Results 1 to 11 of 11

certain words in a cell determine which column is meant?

  1. #1
    Registered User
    Join Date
    01-14-2021
    Location
    IBK
    MS-Off Ver
    2016
    Posts
    5

    certain words in a cell determine which column is meant?

    Hi everyone,

    I want to do a very simple calculation like (x-y)*z, but I've 2 different columns for which could be "y". so I want to make 2 keywords determining which column the calculation should consider.

    I've also tried to explain it in my xlm file.

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019; O365
    Posts
    18,958

    Re: certain words in a cell determine which column is meant?

    In H2 and copy down =IF(G2="TP",(C2-B2)*F2,(D2-B2)*F2)

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

    Re: certain words in a cell determine which column is meant?

    does this formula do what you need

    =IF(G2="TP",SUM(C2-B2)*F2,IF(G2="SL",SUM(D2-B2)*F2,""))
    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.

  4. #4
    Registered User
    Join Date
    01-14-2021
    Location
    IBK
    MS-Off Ver
    2016
    Posts
    5

    Re: certain words in a cell determine which column is meant?

    Wow, that was fast.

    Thank you very much, helped a lot.

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

    Re: certain words in a cell determine which column is meant?

    you are welcome

  6. #6
    Registered User
    Join Date
    01-14-2021
    Location
    IBK
    MS-Off Ver
    2016
    Posts
    5

    Re: certain words in a cell determine which column is meant?

    I've got one more question, I'm sure these are probably pretty easy formulas but I tried for hours and couldn't get it done.

    What if there is one more cell with 2 different words which determines what way it should be calculated with all that formula included from before. Is that possible?

    I attached an example file again so it's easier to understand what I want to ask.
    Attached Files Attached Files

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

    Re: certain words in a cell determine which column is meant?

    You can use an AND() to determing what happens

    SO
    AND( A2 = "L", G2= "SL" )
    for each combination in a nested IF

    IF ( AND( A2 = "L", G2= "SL" ) , Calc1, IF ( AND( A2 = "L", G2= "TP" ) , Calc2, IF ( AND( A2 = "S", G2= "SL" ) , Calc3, IF ( AND( A2 = "S", G2= "TP" ) , Calc4, "" ))))

    what do you want to do for all 4 possible combinations
    A2 = "L", G2= "SL"
    A2 = "L", G2= "TP"
    A2 = "S", G2= "SL"
    A2 = "S", G2= "TP"

    Also will
    Column A ever be blank, and a Calc needed ?
    Column G ever be blank, and a Calc needed ?

  8. #8
    Registered User
    Join Date
    01-14-2021
    Location
    IBK
    MS-Off Ver
    2016
    Posts
    5

    Re: certain words in a cell determine which column is meant?

    I try to do a trading journal so I can keep up with my trades, and for a very long time I did all that manually but that costs me to much time.
    Column A and G will always filled in manually, and they never will be blank.

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

    Re: certain words in a cell determine which column is meant?

    OK, so the formula I suggested will work
    maybe ADD the calculation you need for the 4 options , even if the same

    A2 = "L", G2= "SL" = CALC 1 ?
    A2 = "L", G2= "TP" = CALC 2 ?
    A2 = "S", G2= "SL" = CALC 3 ?
    A2 = "S", G2= "TP" = CALC 4 ?

  10. #10
    Registered User
    Join Date
    01-14-2021
    Location
    IBK
    MS-Off Ver
    2016
    Posts
    5

    Re: certain words in a cell determine which column is meant?

    thank you very much again, took me a while until I managed to enter the whole formula without any mistakes hahaha, but it works perfectly. Now I'm happy and satisfied

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

    Re: certain words in a cell determine which column is meant?

    Ok, IF you have the same formula , then you can reduce the Nesting by using an OR or {}

+ 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. How to determine Row and Column of cell
    By joebbb in forum Office 365
    Replies: 7
    Last Post: 06-05-2019, 07:15 AM
  2. fill in value of a cell in another cell on a different sheet if value meant
    By superchew in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 02-09-2015, 07:11 PM
  3. Replies: 4
    Last Post: 03-23-2014, 08:14 PM
  4. Replies: 18
    Last Post: 02-23-2014, 07:02 PM
  5. Determine highest cell value in column
    By agni452 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-12-2012, 06:49 AM
  6. Find Last Filled Cell, Negate it, determine another cell and subtotal a column
    By kerl in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-30-2007, 06:50 PM
  7. determine the column where a cell was found
    By usadream in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-24-2006, 09:55 AM

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