I have two columns of data that I need to use SUMPRODUCT on. However, in one of the columns, there might be text after the number. The text can be several different characters. I only want to use the numbers, never the text. Also, there are usually blank cells within Column B and at the bottoms of both columns because this formula is going into a template for future worksheets that all have differing numbers of rows.
For the above example, I want the result to be 9879 (14*16 + 40*0 + 20*150 + 97*67 + 13*12 + 0*0 + 0*0 = 9879).
I've tried using the LEFT function to only get the numbers before any text, but I can't make it work with the blank cells at the bottoms of the columns.
It's just a simple percentage calculator for my purposes and works fine as is. However, I want to make it dynamic whereby from a user input the length of the array will increase or decrease, e.g. G9-G11 will become G9-G12 if there are four rows occupied with data. For all my other SUMming equations I have solved this and even came up with what I think is a perfectly valid solution for this one. The one problem is that it doesn't work when combined into a single equation.
My solution for the upper part of the fraction is this:
I have a sumproduct function picking up data from a different worksheet. Because i have to much data it takes too much memory and it doesnt run that smoothly. Therefore i have hardcoded the function and tried to evaluate it using the EVAL() function from the morefunc add-in. However i don't get it to work. It continuously returns #VALUE.
I am trying to obtain price of an item using vlookup() function on the identifier 0003128 (7 digits) stored with custom format 0000000 in one worksheet. However in other worksheet, the identifier is stored as 000312 (6 digits) with text format without the last digit 8. I tried using the left(A,6) function on 0003128 but instead of returning 000312 it returns 3128 and Im unable to use the vlookup() function.
I have three pages of daily data I am working with; orginal source data, manipulated data, and my output. My output data is a daily row of six columns containing 1, 0, or -1, based on my manipulated data (1,1,0-1,1,-1). I would like by row, sum the coresponding source data (5,4,0,-3,3,-4) by the positve 1s and a second column with the negative 1s. then average the column by the number of consituents (positive5+4+3 )/3=4 & negative;(-4+-3)/2=-3.5)
I need a formula that will bring up the Model type of a product based on its Material code. All the models I need to generate have a "root" number in them (the first 6 digits define model type). This is what I need to fill out:
I have some code that run on Excel 2003, and fail on Excel 2000. It happens on functions Left, Right, Mid and Trim. I've found that I must use in "Late Bindings". If exist any convertion for above functions?
I have a formula that uses the max function/formula (I am not sure of the terminology) to summarise the maximum value contained within a cumulative list of monthly expenditure in cells F12 to F60 (i.e MAX(Cashflow!F12:F60)) of a very old sheet I am working on, I was wondering if it was possible to have a cell that displays the profiled expenditure which is displayed in column E adjacent to the cell containing the maximum expenditure.
I have tried using the vlookup formula however combing formulas!
Sample Excel with Frequency.xls.Attached a sample sheet with some data. What I'm trying to do is combine the frequency function and add another variable. Not sure how to do this. I'm using Excel 2003, so I don't think I can use "countifs".
I am having a list consisting of two columns. Column A describes a "product" and the currency directly below. In column B i have the quantity of the product and below the value. Now i have products in USD and EUR and would like to only sum up the quantity of all products which are in USD. A simple example is attached and i calculated the target valua manually. The values i would like to sum are market in red.
I have about 5 nested if statements in 5 different cells. The problem is I need to combine all 5 nested if statements into 1 cell. Can someone show me how this is done. I don't want to try vlookup since the ifs are already written. I named all of the formulas as one, two, three, four, five to help me keep track of them. Need to figure out what I have to do to combine all of the if statements into 1 cell.
I am trying to write a formula using IF, OR & AND combined together, but I can't see to get my formula in the correct order. I have tried moving several sections around but I still keep getting an error.