+ Reply to Thread
Results 1 to 10 of 10

Calculate Benchmarks by checking given cells

  1. 09-14-2021, 09:59 AM #1
    Pauaj
    Pauaj is offline
    Registered User
    Join Date
    09-14-2021
    Location
    Germany
    MS-Off Ver
    16.45 MAC
    Posts
    4

    Red face Calculate Benchmarks by checking given cells

    Hi everyone!

    Im trying to make a sheet that calculates our benchmarks automatically, this is for social media so, so far I have made a formula (ugly looking, but works) to check the media type of the post and the platform (twitter, Youtube etc). And use the benchmark associated to it.

    the formula looks like this so far:

    =IF(AND(C13="twitter",F13="video"),N13/HQ!$C$19-1,IF(AND(C13="twitter",F13="photo"),N13/HQ!$C$18-1,IF(AND(C13="linkedin",F13="video"),N13/HQ!$C$16-1,IF(AND(C13="linkedin",F13="photo"),N13/HQ!$C$15-1,IF(AND(C13="instagram",F13="photo"),N13/HQ!$C$12-1,IF(AND(C13="instagram",F13="carousel"),N13/HQ!$C$11-1,IF(AND(C13="instagram",F13="video"),N13/HQ!$C$13-1,IF(AND(C13="facebook",F13="video"),N13/HQ!$C$9-1,IF(AND(C13="facebook",F13="photo"),N13/HQ!$C$8-1,IF(AND(C13="tiktok",F13="video"),N13/HQ!$C$21-1,IF(AND(C13="youtube",F13="video"),N13/HQ!$C$23-1)))))))))))

    this works, but now I need to do it for different channels. So I would have to add another condition to the formula, and while it is possible, it is probably not the smartest way to do it. Does anyone have an idea how to do this?

    thank you!!
    Attached Files Attached Files
    Last edited by Pauaj; 09-15-2021 at 04:19 AM.
    Reply With QuoteRegister To Reply
  2. 09-14-2021, 09:59 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,916

    Re: Calculate Benchmarks by checking given cells

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 09-15-2021, 04:20 AM #3
    Pauaj
    Pauaj is offline
    Registered User
    Join Date
    09-14-2021
    Location
    Germany
    MS-Off Ver
    16.45 MAC
    Posts
    4

    Re: Calculate Benchmarks by checking given cells

    Thank you Ali!
    I attached it now
    Reply With QuoteRegister To Reply
  4. 09-15-2021, 04:25 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,916

    Re: Calculate Benchmarks by checking given cells

    You need to explain (in WORDS) what the benchmarking procedure is. My instinct is telling me a lookup matrix would be better here, but if you think I'm going to back-engineer that formula, you've got another think coming! You haven't even mocked up expected results in your sample workbook ...
    Reply With QuoteRegister To Reply
  5. 09-15-2021, 04:48 AM #5
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,209

    Re: Calculate Benchmarks by checking given cells

    Try

    in E2

    =$F2/INDEX(INDIRECT("'" & $B2 &"'!$A$7:$E$23"),MATCH($C2,INDIRECT("'" &$B2 &"'!$A$7:$A$23"),0)+MATCH($D2,INDIRECT("'" &$B2 &"'!$A$7:$A$23"),0)-1,3)-1
    Reply With QuoteRegister To Reply
  6. 09-15-2021, 05:18 AM #6
    Pauaj
    Pauaj is offline
    Registered User
    Join Date
    09-14-2021
    Location
    Germany
    MS-Off Ver
    16.45 MAC
    Posts
    4

    Re: Calculate Benchmarks by checking given cells

    I have no clue how that works. But it is actually doing it. Amazing
    Thank you so much!!
    Reply With QuoteRegister To Reply
  7. 09-15-2021, 05:41 AM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,916

    Re: Calculate Benchmarks by checking given cells

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Reply With QuoteRegister To Reply
  8. 09-15-2021, 05:43 AM #8
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,209

    Re: Calculate Benchmarks by checking given cells

    Checking more carefully:Note: it will not work for TikTok / You tube/ Instagram as it assumes that all Platforms have same Media type: need to re-think!

    Check it out yourself.
    Reply With QuoteRegister To Reply
  9. 09-15-2021, 06:30 AM #9
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,209

    Re: Calculate Benchmarks by checking given cells

    Re-formatted table as per tab "TBL"

    Named ranges created

    Data_Tbl , Platform, Media

    in G2 of "Raw Data"

    =$F2/INDEX(Data_Tbl,LOOKUP($B2,{"BQ","CQ","HQ"},{6,10,2}),MATCH($C2&$D2,Platform&Media,0))-1

    Enter with Ctrl+Shift+Enter (Array formula)
    Attached Files Attached Files
    Last edited by JohnTopley; 09-15-2021 at 06:36 AM.
    Reply With QuoteRegister To Reply
  10. 09-15-2021, 08:31 AM #10
    Pauaj
    Pauaj is offline
    Registered User
    Join Date
    09-14-2021
    Location
    Germany
    MS-Off Ver
    16.45 MAC
    Posts
    4

    Re: Calculate Benchmarks by checking given cells

    Thank you John! I saw that the first one was struggling with some channels, but the second one works perfectly. I appreciate a lot your help!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Summarize Student Benchmarks
    By pvanderlinden in forum Excel General
    Replies: 2
    Last Post: 05-02-2017, 11:46 AM
  2. Heat maps according to each individual's benchmarks
    By uocb49 in forum Excel General
    Replies: 1
    Last Post: 07-06-2015, 11:41 PM
  3. [SOLVED] Nested If statement, checking multiple cells for Blank cells
    By JLeague in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-30-2015, 11:18 AM
  4. Any alternate ways to calculate Compound Interest for checking purpose?
    By liau8funny in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-16-2014, 09:45 PM
  5. Best way to chart/present benchmarks vs. Scores comparison
    By amberm0720 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-29-2013, 01:35 PM
  6. Checking time it takes to excel to calculate
    By Alexander_Golinsky in forum Excel General
    Replies: 3
    Last Post: 05-06-2012, 07:07 PM
  7. Checking the Cells in Sheet1 with Cells in Sheet2 and replace
    By Checking the cells in Sheet1 with Sheet2 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-19-2006, 04:29 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1