if

  1. J

    Dates Using IF function?

    Hi First time posting so please be gentle :) I have a set two sets of dates one in column E (Target Date) and one in O (Actual Finish Date) The sheet I have inherited uses the below formula: =IF(O2="",IF(E2>NOW(),"OPEN","OVERDUE"),IF(O2>E2,"FAIL","PASS")) What I need the new formula to do...
  2. M

    Need Help with Match/IF/ISNUMBER problem.

    I have a problem I'm trying to solve. hopefully I explain this right. I'm trying do find a formula to insert 3 missing columns of data from another data set that has those columns. the data with the missing columns has over 600 rows. and duplicate values.. the data that has the columns i need...
  3. W

    Find values in a table greather than zero in a dynamic range of a table

    Hello All, i have a table like a following one (of course, mine is bigger) that is coming from a query: Countries 01/01/2020 01/02/2020 01/03/2020 01/04/2020 01/05/2020 Argentina 0 0 3000 2500 0 Australia 0 1000 0 1500 3000 Austria 1500 0 0 0 3000 Belgium 0 0 0 2000 0 France 1000...
  4. T

    IF Cell Equals a value and has a date in other Cell then Green?

    I hope someone will be able to help me In the attached screenshot you can see that the scope of work (column D) there is a list of transport jobs, if they have the necessary training in columns PMVA, FREC 3, FREC 4, ACA, DCA (Column E to I) which would be a date and it greater than today then...
  5. J

    Conditional grouping and summing of data

    I have a dataset that looks like this: Date Hour Water 1/1/2020 2 5 1/1/2020 3 45 1/1/2020 4 63 1/1/2020 24 35 1/2/2020 1 24 I want to total individual instances of the number of hours and liters. Each "hour" represents an hourly...
  6. S

    Unable to calculate average of values returned from IF function

    Hi, I'm having trouble with the average formula in my excel sheet. I'm simply wanting to calculate the average of three cells, however the average function is returning a #Div/0! error as if the cells are empty. I should mention that the values in the cells I'm trying to average are returned...
  7. W

    If date is in certain month return year range (ex. 2018-2019)

    I need a hero. I have a spreadsheet spread over a few years and am looking for a formula to help with the following. We have what is called a "bid season" that is from October through May. I am trying to have a value returned in column F to combine years if a date in column C is between Oct-May...
  8. M

    IF statement searching for words in a cell

    Hi I know how to write a basic "IF(OR" statement. So, for example, if cell A2 had the text "Ball" or "Racket", and we wanted to return the word "Sports," then we could write this in cell B2 =IF(OR(A2="Ball",A2="Racket"),"Sports","") But this is restrictive, because cell A2 can ONLY...
  9. K

    SUMIF with a range of criteria instead of using SUMIFS and doing one by one

    Hello, I was wondering if there was a way to use the SUMIF function for a range of criteria instead of using SUMIFS and typing each criteria one by one (criteria range 1, criteria 1, criteria range 2, etc.) theoretically the criteria range for my example would remain the same. Point being...
  10. S

    Excel time issue

    Hey guys, I have a issue I have been trying to figure out all day. We have a KPI for a late early arrivals and our KPI if the departure is after 8 minutes of planned time or 12 minutes earlier, it qualifies as "on time". If its more than 12 minutes early it qualifies as "early" and if it is...
  11. D

    How to get a corresponding value based on the most recent date and name?

    Hi, I'm making a stock investment tracker where all you have to do is enter in the transaction and it gives you all the your positions, whether they are open or closed etc. I was finally able to figure out a way to find my average cost per share after buying/selling/rebuying. I have a column...
  12. I

    Adding IF statements to this VBA code

    Hello. I have this VBA code to change the values of cells in column D depending on what time are in the cells in column A. 6:15:00's are STG.A then increasing in sequential order number wise. STG.A01, STG.A02, etc. 6:45:00's are STG.B. 7:15:00's are STG.C. 7:45:00's are STG.D. 8:15:00's are...
  13. I

    How to edit this VBA code include an If statement

    Hello. I have this VBA code to change the values of cells in column D depending on what time are in the cells in column A. 6:15:00's are STG.A01 then increasing in sequential order number wise. STG.A02, STG.A03, etc. 6:45:00's are STG.B. 7:15:00's are STG.C. 7:45:00's are STG.D. 8:15:00's are...
  14. M

    How to get excel to ignore letters in a cell

    Hello All, I have a data set where I need to match some numbers with each other but they have two letters in front of them that make it currently impossible to do. So for example I have: CP2003 in Cell B2 and RD2003.001 in Cell C2. I would like to know how to get a formula that matches just...
  15. M

    Help - looking for advise with IF and AND formula (German wenn / und)?

    Hi MrExcel, I would like to find a easier way using my excel formula and maybe someone can give me some advice. The formula I have set up and is working is as follow (in german)...
  16. A

    Getting number of columns of a particular sheet while looping through sheets

    Hi all! I want to automatically check a number of files whether their column numbers corresponds to the number of columns of another sheet. I have tried to solve that like this: Workbooks.Open Filename:=Path2 & NewList(o), ReadOnly:=True, UpdateLinks:=False For Each Sheet In...
  17. W

    If a date is between 2 dates annually

    I am looking for a formula to determine if a date in column D is between the first day of October through last day of May from 2016-2021. I am looking to do this over several different years (Oct 2016-May 2017, Oct 2017- May 2018, Oct 2018- May 2019, etc.). I have a master spreadsheet with...
  18. G

    Conditional Formatting (If cell value in row column A = 3 or 5 or etc... then cell value B = 1 or 2).

    Hello, I was wondering if I could tell excel to insert a value (1 or 2) along all of column B, based off the values that are in column A. For example If A = 1 or 3 or 5 or 6 or etc... Then values in column be should be 1. and If A = 2 or 4 or 7 etc... Then values in column B should be 2...
  19. Thumper007

    If help

    Hello, I have a formula that I am using to calculate number of days a task has been open. I have added a col (col B) to include a status. Now I am looking to add to my formula either only calculate if the status “open”. Thoughts?? current formula...
  20. A

    Trouble getting a Nested If statement to work

    Hi there First post, hopefully I've followed the rules. I've had a good search but can't find anything specific to the issue I am having. It's very basic so I'm surprised it isn't working. I've attached an image that hopefully clearly explains the issue I am having. Basically I'd like to...

Some videos you may like

This Week's Hot Topics

Top