+ 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, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    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)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    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 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    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 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    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 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    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 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    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