1. P

    Conditional formation in planing calendar

    Hi Forum, I'm reach out to you to ask if someone can help suggest improvement to a function for conditional formatting. I created a calendar where I can plan resources by entering the number of days and the following cells are colored by using conditional formatting. This far I have only...
  2. A

    Quartile formula Multiple IFs across multiple worksheets

    Hi all, I was hoping to get some assistance with a formula I have. I am trying to calculate the second quartile (using multiple if statements) for a specific column across 2 worksheets. Alternatively, I could copy and paste the data into the one worksheet, but I am trying to avoid this. This...
  3. B

    Average of 1 cell depending on dependent of another cell

    Hey, I'm trying to find the average of 1 cell that is linked to another cell. In the image below, I am trying to get the average of the numbers linked to column A - so AAA and BBB. Taking 'AAA' as an example, I first need to find AAA in column B and then take the letters in column C and find...
  4. N

    Date Result returns a "Expired" or "Expiring" or "Current"

    I'm sure I'm just missing something in my statement because i get a formula error, but I'm trying to have my Column "i" look at Column "E" which is a date format. Column "i" would return "Expired" if the date is <today(), "Expiring" if the date is between today and 120 days, and "Current" if its...
  5. M

    Any other ways to loop without using VBA?

    Hi all, I have a set of fixed asset data where the categories the fixed asset are only showed at the end of each category (instead of the beginning). I would need to have the category to be in a new column beside every asset number. There are over 10,000 rows of asset and 20+ categories...
  6. I

    Need help to sum live data in 2 week increments.

    My goal is to understand which two week increments of a marketing campaign were most/least effective. Column A: live data for a marketing campaign (leads generated) Column B: Sum up the live data up until 2 weeks after the start date of the campaign. After the 2 weeks is complete, I would...
  7. F

    SUMPRODUCT IF value equals

    Hello, could you please kindly help me with the below, to make it so that it'd only consider values for the calculation if the corresponding value in column 'A' = "XX", and instead of specifying the range, sums up all the matched values in a given column? Please also note that values start in...
  8. M

    function too short message.

    =COUNTIFS(C:C,"Project Director/Manager-Civil",D:D,"temp",(or(E:E=may-20,E:E=mar-20,E:E=jan-20,"ans"))) i am trying to count the number of "Project Director/Manager-Civil" in column C which has text "Temp" in D and finally on E it could be any from "Jan-20 to dec-20", this would work if i...
  9. V

    Creating a ifs function - spill error

    I would like to create a formula for each customer (column A; i.e. customer 1 made 8 purchases, customer 2 made 20 purchases, etc...) to calculate a ratio in column W based on column V. For example, cell W5 should return 0.75 (3 divided by 4; 3 comes from cell V5 and 4 comes from 4 observations...
  10. E

    Any way to replace "IFS" in Excel 2016?

    I have a deadline tomorrow and I need to change the below code to something that works for Excel 2016. Right now I am using the IFS formula, but it is only working in Excel 2019. Is it any way to rewrite below formula below for the 2016 version or am I screwed...
  11. H

    Creating a macro with If statements for data quality improvement

    The purpose of the macro, is to fix the data where it is invalid. I need to have macro to evaluate a few terms, fix if needed and add a comment of the fix. My logic how to check it goes as follows, Im just not able to write it into VBA. 1.) Marcro runs and checks D column (we could use...
  12. F

    If Statement (maybe) that takes into consideration several factors

    Issue: Need to calculate the times per person, per day using minimum/maximum (or that's how i figured it out). Each day, there could be multiple timestamps. Need to figure out the oldest and newest, then calculate the time in between that based on person and day. Appreciate any help Example...
  13. H

    4 IFs

    I want to fill D2 as per below criteria’s D2=1 if B2>0 D2=0 if B2<0 D2=null if B2=zero 0 or null How to accomplish please?
  14. E

    Formula to Find which category a value falls in

    Hi all, I have a spreadsheet that looks like the following <tbody> Benchmark Benchmark Benchmark Benchmark Benchmark Wages 10th 25th 50th 75th 90th 65,000 100,000 200,000 300,000 400,000 500,000 300,000 100,000 250,000 298,000 350,000 400,000 180,000 50,000 60,000 70,000 120,000...
  15. L

    ifs() not in office 365

    Hi I am reading that ifs() is in excel 2016. I have office 356 and I thought it is updated version of 2016. Thank you.
  16. B

    average ifs/ percentile

    all having some issues with average ifs logic. my formula of "average if" / "percentile" works fine {=AVERAGE(IF('Data sample'!$E$11:$E$211>PERCENTILE('Data sample'!$E$11:$E$211,0.9),'Data sample'!$E$11:$E$211))} HOWEVER, when i try to add "averageifs", i cant seem to make it work...
  17. E

    How to change date by 5 years with a formula?

    Hello, I'm trying to make an excel sheet that tracks my bill payment dates. I need a formula that adds 5 years to the date 27/11/2012 and if that's < today() then add another 5 years and if that's < today() etc. etc. Right now I have 10 IFs just manually adding 5 years to each nested IF...
  18. M

    Rewrite IFS formula - Or create addin to be able to use

    Hello, I have the latest version of excel and created a sheet with a "IFS" formula in one of the sheets. It is a long formula with many if statements. I copied sheet over to another computer only to find out that it was using a older version of office and does not have the functionality. So was...
  19. J

    urgent - nested ifs

    Hi guys, i'm under pressure to get this done in next 3 hours ao any help would be appreciated. lets say I have 4 cells ABCD. The text in A can will either say "Pass" or "Fail" depending on the following: IF (B,C,D = blank ) then A=“Pass” If B not blank but C,D are Blank, then A=“Fail” If B is...
  20. C

    Nested IFs?

    Hi everyone I'm using the below code which works fine: =IFERROR(IF(J29="As Required","Confirm Qty",J29*K29),) But I'm trying to work out how to add another if statement into the above code at the end so that the following also happens: If K29="Item Not Found" then ALSO in L29 print "Update...

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back