I have a workbook which scores tender's, the calculations behind the front sheet work fine, I would now like to show where the tender ranks against a set of criteria. I have attached a sample and typed into the cell D12 what I am trying to with this list of criteria below starting in cell C15.
View 3 RepliesI have a list of data where some of the cell values are the same. I want to rank them uniquely but when I use the RANK function the cell values that are the same are given the equivalent rank. Is there any way of assigning the equivalent values a sequential ranking that is unique?
So in this example rather than producing the rank as this:
100 1
99 2
98 3
98 3
98 3
97 4
96 5
95 6
It would produce it as this:
100 1
99 2
98 3
98 4
98 5
97 6
96 7
95 8
I want to track daily sales of a shop with the tenders (Cash, Master, Visa)seperated.
Everyday there will be a file ctp.dbf from a folder YYYYMMDD (previous day date) which contains sales details.
I tried to use sumif commands and everything is working fine. everytime i have to open book.xls and from it I do a files>Open to open the ctp.dbf for the calculation to be done. is there a way where by i can open 1 file and everthing i calculated properly?
Also this book.xls can only do for 1 day how can i go about having the daily sales detail of the month (look something like sales summary.xls) or even year in 1 excel file?
attached is book.xls and sales summary.xls for reference.
See attached example for reference - I am trying to calculate an average if it meets 2 conditions, i.e, calulate the average for the Lead Time column if it is a Bag and On Time. Hope you guys can come through as always!
View 4 Replies View RelatedI've got a column with client codes (col. B) and another one with material codes (col. C). Both columns mix numbers and text. First condition: If cells in "B" column equal a specific code ("DA2")
Second condition: AND If the cells in "C" column start with a certain pattern ("30") followed by some other pattern (four random digits, a dash, and two or three more random digits). THEN a new cell should say "T1", Else it should just copy another cell.
Here are the examples (pictures):
Example 1 Before
Example 2 Before
You can see that it's an history of currency trades (Forex). What I want is to see how much money one would have made letting only one open position per currency pair. A basic algorithm would be something like this:
I have an interesting problem on my hands. I work for a gas station, and for our cigg orders, we fax an excel speadsheet to SAMS CLUB. I recently have done some modifications by applying conditional formating to indicate negitive numbers and zeros, however, I am trying to find a way to blank out 2 adjacent cells when a cell = 0.
Here is a sample:
Order# Desc #to order
921934 (brown) Bx 0
The Zero is already whited out in my sheet, however I am trying to find a way to make the Order Number and Desc. be whited out also when the Number to order = 0
So, Excel reads my formula. It tells me it's correct, but it has too many statements. [I know this is true, because the equation is absolutely huge. )
The thing is, I need it to tell me, based on two separate columns - one being "State" and the other being "A vs. O", who this contact belongs to. [For each state, there is a different person for A and for O, and the entire spreadsheet is text.]
My ridiculously large, almost functional function is:
=IF(AND(RC[9]="WA",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="WA",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="MT",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="MT",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="ID",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="ID",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="OR",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="OR",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="CA",RC[13]="APP"),"Tom Wieske"),IF(AND(RC[9]="CA",RC[13]="OPP"),"Tony
[Code] ....
As you can clearly see, there are a lot of terms, but I need both the "State" and the "A vs. O" column to determine the outcome of the column the formula will be in.
I'm trying to add "yes" as per the attached spreadsheet, using forumla. Account codes are duplicated (sometimes more than once, although no shown on the example) each account code needs to have the same yes column.
View 3 Replies View RelatedI setup a Vlookup formula (in sheet 1) to find the figure in column C (in sheet 2) based on the company name and branch name but the result is incorrect.
View 3 Replies View RelatedI am experiencing a big block on this one...I am trying to find the sum for a group of cells in a particular column given that MONTH and YEAR (as stated in two separate columns) match the date that is displayed in, lets say for example, A2. below is a sample of the data I am refering to.
************************************************************************>Microsoft Excel - Investor_Portfolio_TEMPLATE.XLS___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA12=
ABCDEF1DateAmountYearMonthSumofAmount23/21/05990002005Mar33/21/0599000Apr46/8/0593000May56/9/0599000Jun63/23/0599000Jul73/23/0599000Aug81/4/0699000Sep91/5/0699000Oct101/6/0699000Nov112/1/0699000Dec122/5/06990002006Jan134/5/0699000Feb144/7/0699000Mar155/2/0699000Apr165/8/0699000MaySheet2
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.[/url][/code]
I have a spreadsheet with data in columns A:K.
I want to be able to change font color for a row based on what is found in column J and K. In other words if J3 contains the letter M then row 3 should be Green. I know how to do this using conditional formating, however I have 2 problems.
First, I have 6 different conditions to meet.
Second, there is one more condition to meet. If the date in column K3 is older than todays date then row 3 should be turn Red regardless of what is found in column J3.
jamm
I'm trying to build a formula on the matching text as below, im getting an value error, however when i press the FX key it shows the correct result.
Column A
Column B
Column C
Column D
[Code] ....
( I would like to match the below two criteria with the Row 1 and Row 2)
Co1
#VALUE!
Latest Forecast
Formula-->
IF(MATCH(A6&A7,B1:I1&B2:I2,0),"Ok","Not ok")
I have three reference cells (a1, a2, a3).
Below, starting from b4 to l4 I have years from 2005 to 2015, row 5 for the same range contains data, row 6 (c to l) contains a growth rate.
I need to come up with a formula that can multiply (x * y * z *... [product-like formula]) the growth rates for all the years where the current year (row 4) is bigger than reference the reference year in a1 or smaller than reference year in a2. A3 is then used to multiply that total.
I have attached a sample of my data. What I am looking for is the best (most elegant solution) to my problem.
I am trying to create a macro where I have 2 sheets. Sheet 1 will contain my data, sheet 2 will contain a single column of criteria to search through sheet 1.
I would like for the macro when it finds that criteria, it will delete all rows of data that is tied to that ORDER_NUM
Schedule
EFGHI1ORDER_NUMAREADL_DATESHIP_CODEPART2113510395CANADA EMPACT20100108IN445N3113510395CANADA EMPACT20100108IHT9534113510395CANADA EMPACT20100108ID981C5115436953CANADA EMPACT20100110IPMDMN6115436953CANADA EMPACT20100110IN246F7116292017CANADA EMPACT20100111IY1G0M8116411203CANADA EMPACT20100111IT571R9119169642CANADA EMPACT20100113I9M62C10119169642CANADA EMPACT20100113IT272C
For example, it my criteria was part N445N, I would like for it to delete all rows of data that is tied to that ORDER_NUM (113510395).
Desired Output
Schedule
EFGHI1ORDER_NUMAREADL_DATESHIP_CODEPART2115436953CANADA EMPACT20100110IPMDMN3115436953CANADA EMPACT20100110IN246F4116292017CANADA EMPACT20100111IY1G0M5116411203CANADA EMPACT20100111IT571R6119169642CANADA EMPACT20100113I9M62C7119169642CANADA EMPACT20100113IT272C
I have previously been performing a multi level SUMIF of named ranges with a large data range.
This works but its inefficient as it would be better to preselect a subset range before the SUMIF begins matching criteria.
Basically, i'd like to select a range of rows which will parse into the SUMIF formula.
I have solved this by referencing an external cell to create the range.
I am using the following piece of code to 'link' a value from a cell in one workbook into another.
ActiveCell.FormulaR1C1 ="='[filepath]Front Sheet FIT'!r7c4"
However, i have come across a cell which displays the result of a reference to another cell (something simple like =b32). Normally I would just link to b32, however, the macro has to be applied to a large number of documents and in some cases text has been specifically entered, while in others the reference has been used. Is there any way of using VBA to display either the result of a formula (if there is one) or text if this is the case?
formula to calculate the commission based on 9 cases each case is subjected to 4 differant arguments.
here is the formula for the first case (the answer if true 1.4) & the last case( if all the casses are fails it should show 0.6) u will find it in cell K5
=IF(AND(E5>=20,H5=1,I5>=80,J5>=80),1.4,0.6)
this is only 1 case HOW can i calculate it basd on 9 casses??
What formula should I put in NUTRISTATUS column. That will search value in the table of MALE BMI and FEMALE BMI. for example when I input data M(male) in ***(gender), age in M(m0nth) and BMI it will search in MALE BMI table..
and I input data F(female) in ***(gender), age inM(month) and BMI it will serach in FEMALE BMI table.
and if the criteria match will appear the word SEVERLY UNDERWEIGHT or OVERWEIGHT or UNDERWEIGHT or NORMAL or OBESE in NUTRISTATUS Column..
I am using a list like this.
A
B
C
D
1
Teacher
Name
Color
Size
[Code] ....
I need formulas that can evaluate the table above and provide the information below. If there needs to be multiple steps/formulas, I'm okay with that.
Two sticking points, BOLD - be listed twice with the same teacher. I don't want them counted twice in the "Size 3" column. BOLD & italicized- same student could be in two different teachers' classes. They need to be counted under both.
Teacher
Red
Green
Blue
Size 3
[Code] ...........
Been trying to figure out whats wrong with my formula. The 4th column doesn't seem to produce the intended result. I have attached here for reference.
View 4 Replies View Relatedi tried to compare 2 different cells with text inside.Each cell contains "yes","no"," ", 6 possible values
For example
A B C
1 YES NO
2 YES YES
3 YES " "
4 NO YES
5 NO NO
6 NO " "
In column C, i want to enter the result f.e. if a1="yes" and b1="yes" do 1,if a2="yes" and b2="no" do 2 and so on There is a function for this?
Need to rank by score1 in descending order and Serial # in ascending order. My biggest problem is to get the rank number to be consecutive. I have listed the rank I would like to see.
Score1
Serial #
Rank
90
146168
1
[Code] .....
I have a large worksheet that lists employees and the hours they work each day in a pay period.
For Example:
Column A holds a date and Column E holds the number of hours worked for a paytype. So i want to create some function that sums the numbers in column E, but only those where column A holds a date.
Then i want to return that number to a cell, say C20. I think I've managed to tell the macro which rows i want to return values from, but I can't figure out how to say "add these numbers together and put the total in C20."
I'll check this frequently if clarification is needed.
Trying to count the number of fields that meet a criteria base in 3 different columns. Worked on this for few hours but still keep hitting the wall of no right answer. I have attached an example sheet.
Trying to find the Number of fields that are not blank in the range of B9:B28 and have no date (are blank) in range(C9:C28) and have a number that is < 7 in range (D9:D28).
I consider myself a beginning intermediate Excel user and am really learning a lot through this forum but have not run accross the answer to what I'm trying to achieve. I would like to return the value in the "Temp" column that meets all the conditions of the columns "letter", "day", and "time." Specifically, the "Temp" of "A", "Weekday", "East." I have attached a small spreadsheet. It seems Vlookup is the approach to use in combination with multiple if conditions or an array. But I am not yet comfortable with such a complex equation. Can someone provide me with some guidance or an equation that would work. So far this is how I've been learning. Looking at equations and deciphering how they are written.
View 5 Replies View RelatedI would like to write a formula for shipping cost in Excel 2003, for specific shipping weights. Each product is a different weight. The result is in a new column.
What formula would I use for this column? The figures below are hypothetical.
A1 is 4 lbs. Shipping is $6.00
A2 is 8 lbs. Shipping is $12.90
A3 is 10 lbs. Shipping is $16.00
A4 is 15 lbs. Shipping is $24.00
Formula for the column is ?
If A1 is greater than 1 lb. and less than 5 lbs. then shipping cost is $6.00 or
A1 is greater than 7 lbs. and less than 10 lbs. then shipping cost is 12.90 or
A1 is greater than 9 lbs. and less than 15 lbs.then shipping cost is $16.00
A1 is greater than 14 lbs. and less than 18 lbs. then shipping cost is $24.50
I'm trying to calculate a multi-variable equation based off of data in a table with different criteria. If you reference the attached spreadsheet I am trying to calculate cost based on weight and zone. For the data entered in cells B1 and B2 944.01 would be the cost. If you look at the table in G2:N3 you'll notice that the values in G2 and G3 are the weight limits G2 is from 0-4999 and G3 is 5000+, this should impact where the value in B1 falls. B2 should select from H1 to N1.
View 3 Replies View RelatedExcel 2007.
I have a workbook with a sheet per day, each sheet has a variety of 'jobs' as columns and a variety of people and kit as rows. Hours are manually input each day during the month.
Simplified version... These are two separate sheets 'M060114' and 'Tu070114'.
Monday
Dig
Drive
Dave
4
5
Bill
2
7
Tuesday
Dig
Drive
Dave
2
7
Bill
8
1
I have a Pivot Table on a separate sheet. This was created using the Wizard (ALT +D +P).
Dig
Drive
Dave
6
12
Bill
10
8
So far, lovely.
But when I double-click on a number or right-click and choose 'Show Details' I don't get the sheet names in the newly created information sheet. I get the Row label, column label and a list of the entries.
So if I clicked on 10 above...
Row
Column
Value
Bill
Dig
2
Bill
Dig
8
That's fine with just a couple of entries but with about 80 columns and 250 rows spread across a month, therefore 30ish sheets, it's difficult marrying up the 'Show Details' sheet to the actual data.
So after all that, the question. Is there a way of getting the sheet name to appear in the 'Show Details' information sheet?
A most of time I'm using VLOOKUP function. I want to call specially this function by pressing custom button (w/o pressing "Insert Function" and choosing VLOOKUP)
View 4 Replies View Related