formulas

  1. W

    VBA hard code formula problems

    Hi! I have a big workbook that evolves around a cell (O3) In this case O3=11, but this number/cell changes several times during a working day. When running the macro, I need the vba to hardcode a formula to A2 with a number from cell O3 The working formula is: =IF(sheet1!B11=0,0,sheet1!B11)...
  2. A

    Help with multiple index matches with multiple if statements

    Hi, I'm trying to write a index match formula with 2 if statements. So far my formula is =IF(I3>H3,INDEX(zb!C:C,MATCH(1,(I3=zb!D:D)*(G3=zb!H:H)*(H3=zb!D:D),0)),(IF(H3>I3,INDEX(zb!C:C,MATCH(1,(H3=zb!D:D)*(G3=zb!H:H)*(H3=zb!D:D),0))))) I want to return the data if I3 > H3 then match the data...
  3. Jyggalag

    Have formula data appear as text, maybe VBA solution?

    Hi all! I currently have this setup, please note that I have hidden all the nonrelevant columns however: The formula I use is this (thank you @RoryA !) =IFERROR(INDEX(INDIRECT("'"&H504&"'!$U$3:$U$73"),MATCH(1...
  4. M

    Dynamic Formula

    Hi All, I want to dynamically link the entry in a cell to another formula - I tried using the indirect function, but cannot seem to get it to work. Is this possible - what I am trying to get in Cell B4 is the number 5, and not "2+3". When working correctly I should be able to change Cell C2 to...
  5. T

    Excel formula for financials with a "broken year e.g, March to December"

    Dear All, I would like to kindly ask for your assistance on a bottleneck that i am facing the last couple of weeks in excel and can't solve myself. I have attached a simplified version of my model just for illustration purposes in order to make it easier for someone that could assist me. So...
  6. Jyggalag

    Conditional formatting for blank cells containing formulas?

    Hi all, I currently have this setup: I have a row of formulas saying IF(ISBLANK(cell below),return blank value, otherwise return value to the left + 7) to advance the week one week ahead when data is entered. I want to create a conditional formatting for this row, so whenever a value is...
  7. Jyggalag

    Pivot Table with Query

    Hi All, I am currently facing an issue in with my pivot table that I have been absolutely unable to fix and it does not seem like the Power Pivot forum is very active compared to this one. In addition, mine is more related to Pivot Tables than Power BI, Query or anything like that, so I may...
  8. Jyggalag

    Refer to range in VBA Excel (macro)

    Hi All, I am having an issue in Excel. My current setup looks like this: However, if I put range("O2") it works fine and it sends out the email to my email address. But once I try to increase the range and make it send out to multiple emails, it comes with the following error (highlighting...
  9. S

    Creating an Appended Combined Column to use as a database

    Hi all, I’ve been struggling with this for some time because I need to use formulas to automate the new database column, not VBA or power query. Essentially what I want to do is create a column that combines two other columns but the difference is appended to the bottom. For ease of explanation...
  10. A

    Conditional formula

    Hi after a formula for conditional formatting In column A I have dropdowns where I select an item. Col B.C,D are amounts Col E is a final amount I want it so in Col E the formula is if Col A sees a word then when Col E is greater than 80 it will be green. If that makes sense :)
  11. M

    Applying time period to a DAX formula?

    Hi there, I am currently using the following two measures with a power pivot table: Sales Count =DISTINCTCOUNT(SalesHistory[Transaction ID]) Sales Frequency (return a “2” if there are at least 3 sales instances, “1” if instances of sales is between 1-3, “0” if no sale) =if([Sales...
  12. B

    Formatting months within a quarter

    Hello, I have a simple data validation of each quarter in a year, C4. Once the quarter has been chosen it displays each month within that quarter in cells: F4,I4,& L4. Currently these months which are displayed are not in date format. How can I get them to be dates and not general text. Also...
  13. N

    combine two formulas?

    I am trying to combine two formulas in excel, one to determine if a vendor is one of two options (the vendor number starts with M or not) and the other formula is to count the number of cells that are not blank. I basically need to count if a cell has data and then separate this into one of two...
  14. Jyggalag

    Issue with Index&Match formula

    Dear all, I am currently trying to return some data from a larger database. I have created a test sheet that is very similar to the real sheet that I am working with, for the sake of privacy and making things easier to understand from an outside perspective. Currently, I have the following...
  15. R

    Google Sheets - Add Blank row after every 100 rows of data

    Hello, Just wondering if any knows if / how this can be done in Google Sheets. 10 colums of data with 1,000 rows on a Google Sheets Document What forumula can I use to automatically insert one or more blank rows after every 100 data filled rows to break up the large block of data? Is there a...
  16. G

    FILTER Function

    Ok, so this is a follow-up to a previous thread which I thought I had solved and turns out I was just really tired and not paying attention to what I was doing. Apologies for re-introducing the question. I have 4 tabs worth of information: 1) Site information (Named table: T_Branches)...
  17. D

    Weighted Average Rankings dynamic selections of which values to count

    Here's my spreadsheet. Objective: Get a [cumulative] Weighted Ranking of Plants by including only specific individuals' ranks for plant types. Disclaimers and needs to keep in mind: 1. No Visual Basic / Macros 2. I'm using Excel 2010, which means no FILTER, along with a few other functions...
  18. L

    Excel problem

    Hi guys can you please help me in doing points: 1,2 and 3 of the assignment here below: (I also added below the excel file)
  19. G

    Comparing Day of Week with Date from Previous Years

    Good day, I'm having a struggle trying to figure out how to do something regarding DOW and Dates. For example, in the dataset I have Sunday, August 1s 2021 has the number 100 and I want to compare that against the first Sunday in August 2019, not against the 1st (date) itself because that is a...
  20. S

    Conditional formatting for a worksheet that is vertical to another worksheet that is horizontal.

    I have one workbook. I have a data entry sheet where users can enter data (A3:H866) - vertical orientation. I also have a summary sheet (AB6:AM90) - horizontal orientation where I have used an Index formula to bring in values from the data entry sheets. The expectation is that if there is a...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

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
Top