+ Reply to Thread
Results 1 to 8 of 8

If formula calculation if adjacent cell contains text if not leave blank

  1. #1
    Registered User
    Join Date
    07-27-2017
    Location
    Dublin
    MS-Off Ver
    2013
    Posts
    4

    If formula calculation if adjacent cell contains text if not leave blank

    Hi all,

    I'm new on here, this has possibly been answered here already but I cant seem to find something that works for me.

    I have an expenses sheet with a number of diff categories and sub categories, within some of these sub categories items are payable per no of times the task is completed.

    We have an issue with one category with some people entering amounts in the total column which clearly don't relate to the approved amount payable per task/subcategory.
    I have added a column which calculates the approved amount based on the information input.

    I want to add a check that says if the category = x and amount in the total column = the approved amount then the return is blank/true but if the approved amounts don't match false/check calculation appears.

    I have a basic IF formula in which is working for the calculations amount match but I think i need to nest it to allow for the other categories which is where I'm struggling.

    My difficulty seems to be that some categories don't have a calculation validation as these are accommodations made on a case by case basis, So I need to say if the category = y or z then just return blank/true.

    Sorry its probably a simply solution! I just cant seem to hit on it.

    Thanks in advance for any advice

  2. #2
    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,981

    Re: If formula calculation if adjacent cell contains text if not leave blank

    Welcome to the forum!

    Please provide some sample data.
    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.

  3. #3
    Registered User
    Join Date
    07-27-2017
    Location
    Dublin
    MS-Off Ver
    2013
    Posts
    4

    Re: If formula calculation if adjacent cell contains text if not leave blank

    capture.PNG

    Formula in column check calc =IF(A9=$A$6,F9*D9+G9*E9," ")

    Formula in next column =IF(C10=I10," ","check calc")
    Last edited by miss freckles; 07-27-2017 at 02:08 PM.

  4. #4
    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,215

    Re: If formula calculation if adjacent cell contains text if not leave blank

    Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Registered User
    Join Date
    07-27-2017
    Location
    Dublin
    MS-Off Ver
    2013
    Posts
    4

    Re: If formula calculation if adjacent cell contains text if not leave blank

    Okay here goes...hopefully this works
    Thanks for the instructions JohnTopley
    Attached Files Attached Files

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,064

    Re: If formula calculation if adjacent cell contains text if not leave blank

    I'm confused:

    If catergory = Reviewer and amounts match - blank
    if category <> reviewer, OR amounts don't match - check calc?

    is that the logic? If not, what is??? BtW, Hi from cloudy County Down...
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: Lá fhéile Pádraig sona dhaoibh

  7. #7
    Registered User
    Join Date
    07-27-2017
    Location
    Dublin
    MS-Off Ver
    2013
    Posts
    4

    Re: If formula calculation if adjacent cell contains text if not leave blank

    Yep thats the logic is it as simple as <> reviewer??

    I knew i got out of bed on the wrong zide this morn!

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,064

    Re: If formula calculation if adjacent cell contains text if not leave blank

    So the formula becomes:

    =IF(OR(A9<>$A$6,C9<>I9),"Check calculation","")

    In which case, your expected answer was incorrect...
    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. Replies: 1
    Last Post: 06-29-2016, 03:44 AM
  2. IF cell is this text leave blank otherwise keep original text
    By king10001 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-17-2016, 07:06 AM
  3. Put an X in a cell if there is Text in a Range of Cells. Leave blank if not.
    By pschultz614 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-09-2015, 05:37 PM
  4. Replies: 2
    Last Post: 10-24-2014, 12:12 PM
  5. [SOLVED] help on a simple if text says this input that, if cell is blank leave it blank
    By Rec1ne in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-02-2013, 10:43 AM
  6. [SOLVED] Simple pull exact text from cell, if blank it pulls up a 0. How to leave it as blank?
    By sharpmel in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-30-2012, 02:20 PM
  7. Leave #N/A blank until a calculation appears
    By kukarooza in forum Excel General
    Replies: 6
    Last Post: 08-17-2011, 02:05 PM

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