I have a table that gets updated based on a couple of data sources that are manipulated in the query editor in excel. That data is referenced by another tab for a process in my company. The table will fluctuate in # of rows, but shouldn't exceed 400. In the tab that my company uses, I want to...
Hi All,
I have a small problem.
I need to sum a range of numbers and subtract a constant.
I have used the method: {=SUM(B4:BUU4-0.5)}, which gives me a #VALUE error. The problem occurs since there is empty cells in this range. I have also tried with a {=SUM.IF(B4:BUU4,">0",B4:BUU4-0,5)}, but...
Hi all,
I want to check the correlation between two columns (column B and I), taking out all the values that give an error. Furthermore if a certain nth value in column B (I) gives an error, the corresponding nth value in column I (B) should also be ignored. I did it this way but it gaves a...
Hello guys
I wasn't expecting to post a question here, but here it is:
related to another post here (https://www.mrexcel.com/forum/excel-questions/1090639-formatting-cell-value-based-another-cells-colour.html) I decided to make a small UDF to determine the color of a cell.
At first it all went...
Hi All
I am creating a formula but am having an issue which im not sure how to correct.
My formula is below:
=TEXTJOIN(", ", TRUE, IF(((MONTH(A1) >= MONTH(Sheet1!B2:B26)) * (MONTH(A1) <= MONTH(Sheet1!C2:C26)) + ((MONTH(A1) >= MONTH(Sheet1!D2:D26)) * (MONTH(A1) <= MONTH(Sheet1!E2:E26))))...
Hi!
I was tasked to create a relatively simple series of excel workbooks that connect to a main workbook.
I have several different districts that are gonna have their own budget workbook aswell as a report workbook from which the person in charge of that district can send out a small report...
Hi there,
I am trying to construct an IF Function for the following problem:
Data:
5 collums of data (A,B,C,D,E) and 33 participants.
Conditions:
define person as 'high risk' if either '3 out of 4 values (A,B,C,D) are smaller than 10' OR if '2 out of 4 values (A,B,C,D) are smaller than...
I can't figure out why I'm getting this error only on this one row. All my other formulas on the Revenue Summary sheet work fine.
{=SUMPRODUCT((MONTH(InventoryTracking[Date Sold])='Revenue Summary'!M2)*(InventoryTracking[Sale Price (Before Tax)]))} **no problems with this one**...
Hi,
I'm trying to create a keyword search function, that uses a named range called "kwmap". It creates a string array from the found keywords and maps them with vlookup to the corresponding number (solution ID). After that it should compare the amounts of each ID and set the result to the ID...
Hi so I have a reference issue with my Vlookup and I'm not sure why. All the data seems to be matching and the format of the cells match up.
If you look at sheet 1 column B should be looking up and copying information from Sheet 4 which should then have sheet 1 column E pull information from...
Good morning!
I did a bit of searching to solve this on my own but alas, I have been unable to.
I am using a formula to determine how many of these characters exist in a cell:
=SUMPRODUCT(--ISNUMBER(SEARCH(Sheet2!$A$1:$B$28,Sheet1!H2)))
The formula is by default evaluating to 3 and I don't...
Hi,
I am trying to write a formula to count the number 1 over a set of cells in each sheet into a totals sheet at the end.
I have named a sheet First and Last at the relevant places to help with the formula but get a #VALUE! error when I try and get the result...
I am trying to write UDF in VBA that will give me a the y-coordinates to confidence bands for a linear regression of data. I am new to programming with VBA but I have scoured the internet in order to try and figure out what I'm doing wrong, but I cannot figure it out. I basically can't get the...
Hi there!
I am having a problem in finding percentage.
In cell B3 I have a text-number string like "Science580/1050", "Arts550/1050", etc respectively. To find percentage by dividing 580 by 1050 I developed a formula copied below. Please note that in above example i.e. "Science580/1050"...
Looking for help on a relatively simple (i thought) formula for doing two things in one cell. I have a 2 sheet workbook. On Sheet 1 i have two cells (A1 and A2) with NAMES OF EMPLOYEES in them. I want to transfer that info from Sheet 1 to Sheet 2 to let's say Cell A50 on Sheet 2 but with the...
Hi Everyone,
I have a large set of data in my excel spreadsheet with 9800 rows.
I want use RANKIF function by comparing two columns. when I am trying to solve the problem using the below function
=SUMPRODUCT(--(H2=$H$2:$H$9692),--(AZ2<$AZ$2:$AZ$9692))+1
it shows me a value error. I think due...
Hey all,
So I have a few formulas that run in the background of a workbook that is used at my work and keeps track of progress/amount of work done by different people in my department. I am trying to make some tweaks/additional adjustments to the formula, and it's getting hung up but I can't...
It is probably a simple answer that i just have not been able to see.
I have converted everything in my spread sheet to inches, then did the formula to find square ft. - No Problems there
What i need to do is " lets say A1 = 66 and A2 = 66 The Sq. Ft. it gives me is A3 = 30.25. I need to...
I have a workbook that has two sheets. Sheet one is in color and there are six timecards on it that all fit on one screen. Sheet 2 has those same six time cards, organized vertically instead of horizontally, and all the color formatting has been removed for printing. The idea is that everyone...
Excel 2007.
I have a workbook with three main sections that share information.
1. Project sheets. These have random sheet names and contain project information. In cell S16 I am calculating a start date based on project type and status. I am checking to see if cell 108 is filled in to...