I use excel every once in a while and have come to a nice little problem here. I am sure this has already been answered but really have no idea how I would go about searching to find it. I attached a portion of my sheet to better understand what I am looking for. As you can see the name of this sheet is 100-150.xls and this is for average tickets. I have some that go up to 500 at 50 dollar intervals. I would like to consolidate these into on sheet and the individual cells amounts will depend on the "Average Ticket"
I do not mind making an IF statement for every cell that I need to but maybe some other ideas for something different that will work are fine as well. Basically the number that goes into cell B18 is dependent on the average ticket and the number of transactions. So depending on the average ticket this determines how many transactions that will be listed in the range of "B" cells down the sheet.
So for cell B18 I would like an IF statement that takes into effect the different average tickets. I think it would look something like this. =IF(B18???? <---- I will leave this like this and work on the rest of the statement when i have an answer to this. The question marks should represent a way for the statement to see that the average ticket is between 100-150 and them multiply the number of transactions by .085 and that is number of transactions that go into B18. The .085 changes with different average tickets but I will deal with this by nesting the IF statement together for each "B" cell.
I have three IF statements as below. the problem is if the first statement is true I want it to skip the next two statements or the result will be changed again.
DATE A B C D E 2/22/2008TRUEFALSEFALSEFALSERon 2/23/2008FALSETRUETRUETRUEPhill 2/24/2008FALSETRUEFALSEFALSETracy 2/25/2008FALSEFALSEFALSEFALSESharon 2/26/2008TRUETRUEFALSETRUEBill
On sheet two I need to list any date that has three or more true statements with the coresponding name.
How do you write an If then statement using a range of cells? I want to construct a logic test using the range of cells A7:A19. I want the logic test to see if the any of the values =1. If the test is true then I want it to display the value in B7:19 (which ever cell corrulates to the cell in column A that has the value of 1) and display the number in the B column.
I'm going to be using a spreadsheet to keep track of where different people are at. So if Person 1 is in Room 3, I will stick a 3 in the box next to their name and then can look at the spreadsheet whenever I need and see what room they are in. When I'm deciding what room to put a person in, though, I need to be able to quickly glance at a list of Room #'s and see what one's are still available. So I have a bank of Room #'s in the spreadsheet....1,2,3, etc.
What I'd like, is some way to set this up so that when I put, for example, "3" in the cell next to "Person 1" the spreadsheet automatically removes "3" from the bank of available Room #'s and when I delete the "3" because the person has left, it adds "3" back to the bank of available Rooms.
I have a mass of data which look something like this:
table removed
and I require the ranges of reference numbers to be listed in a column one above the other, which requires inserting new rows. I also need the date & description columns copied down into the newly inserted rows.
So basically for example I would want the top row to now read:
table removed
and then apply the same procedure to the other ranges below this.
I use macros to print pages, depending on the number of entries I have. If I have 1500 entries, I have to have 1500 If statements. Is there a way to write VBA in a macro to where I can refer to a cell and use the value of that cell to print the range.
make this formula more concise and shorter, it was design to check a numbers in a range to see if any of them falls into a particular range.
=IF(SUM((COUNTIF(Fund,">11999")-(COUNTIF(Fund,">12999"))),(COUNTIF(Fund,">21099")-(COUNTIF(Fund,">28729"))),(COUNTIF(Fund,">28730")-(COUNTIF(Fund,">33999"))),(COUNTIF(Fund,">58999")-(COUNTIF(Fund,">59999"))),(COUNTIF(Fund,">82000")-(COUNTIF(Fund,">84999"))),(COUNTIF(FUND1,">11999")-(COUNTIF(FUND1,">12999"))),(COUNTIF(FUND1,">21099")-(COUNTIF(FUND1,">28729"))),(COUNTIF(FUND1,">28730")-(COUNTIF(FUND1,">33999"))),(COUNTIF(FUND1,">58999")-(COUNTIF(FUND1,">59999"))),(COUNTIF(FUND1,">82000")-(COUNTIF(FUND1,">84999"))))>0,"ATTACHMENT E IS REQUIRED","")
and if the any of the number fall into the range it will print the message, "Attachment is Required"
I have a range of values in column A, I wish to add all numbers by all other numbers, i.e for a specific value in the range, I want to add it to all the other values in the range, and output the values in a new range.
For example for values 1,2,3,4 in range A2:A5, I want to output a new range the values;
1+2 =3 1+3 =4 1+4 =5 2+1 =3 2+3 =5 2+4 =6
e.t.c, beginning in range B2. No number can be added to itself.
I have the following columns and rows that I need to MOD by 15. Once they have been MOD, I need to set a range and find all numbers in that range of say, eg; all number between 9.6 and 9.8. Really hope someone can help, have been working on it day and night. I can do them one at a time, but is very time consuming. Is there a formula that will do the whole worksheet in record time?
2/18/1975, 156.7519, 175.8083, 235.4675, 253.765 4/11/1973, 237,7642, 346.5342, 113.3145, 321.435 5/12/1985, 342.7625, 123.4233, 253.4321, 357.234 etc. etc. etc.
I tried excel help and the great OZ but nothing exactly to my problem.
This is what I would like the formula to do.
Cell S5 would search the solutions cells( K5:O7 ) and compare them to either cell ( R5 for all 3 matching digits) or all combinations cells ( D5:I5 ) if a match is there then it would produce a "Win" if no match it would produce a " Lose "
I will need to be able to expand my range because my data will extend in the solution cells
Looking for a macro that takes a range of numbers lets say A1 to A20 and goes down the list adding up the values to the point the sum becomes equal to 6. If the sum is greater than 6 then it backs off one. The sum should be entered in the cell next to where the 6 appeared or where it backed off. Exmaple if A1+A2+A3= 5.3 then the answer 5.3 is entered into cell B3. The trick to this is that I want this formula to loop until A20 is hit.
I'm attempting to create a formula that'll sort a range of numbers and then return the corresponding values based on a specific place within the sorted range.
In this case, I'd like the following numbers sorted smallest to largest:
15, 9, 11, 19, 22, 17, 24, 19, 17, 24
The result would return something like:
9, 11, 15, 17, 17, 19, 19, 22, 24, 24
Then I'd like it to return the value based off of the formula in cell U13 & U14. In this case, it'd return the value in the 2nd and the 9th position (bolded above). I tried to use INDEX(), but it's returning the values in the unsorted list. I'd like it to return values from a sorted list. Also, I'm trying to solve this using a formula (not Sort & Filter) or VBA.
Is there an easy way to round a range of numbers up? I have a big list of prices and I would like to keep the two digits after the decimal point as .00, but round up the main number. And I would like to do that without writing a formula in every cell. Is this possible?
I have tried over and over to get a point value from the percentages posted below but my formula will not return consistent results. Am I doing something wrong.
94% - below 1 Point 95%-96% 2 Points 97% 3 points 98%-99% 4 Points 0% or 100% 5 Points
Reference Cell is AG2 and is .99 but for some reason I return a 5 =IF(AG2=0,5,IF(AND(AG2>0,AG2<=0.94),1,IF(AND(AG2>=0.95,AG2<=0.96),2,IF(AG2=0.97,3,IF(AND(AG2>=0.98,A G2<=0.99),4,5)))))
A cell that will count an entry in another cell, within a range of numbers. EX: cell g1 will count the entries that range from 1000 to 1999 and 5000 to 5999 and 7000 to 7999. cell h1 will count the entries that range from 2000 to 2999 and 6000 to 6999 and 8000 to 8999. So if i had 6 cells with the following entries:
I'm making an eligibility chart based on income. If 1 person (equals a household) makes between $0 and $1000 (household income) they are in "x" category. if that person makes between $1001 and $2000, they are in "y" category. If that person exceeds $2000, they are in "z" category. So I have 3 categories they can be included in (x=Free, y=reduced, z=paid...it isn't just a true and false statement). The tricky part is the number of people in the household. 2 to infinite # of people making in the range of $0-$1000 need to be in "free" category. 2 cells are being drawn from. A1 is the number of people, A2 is the income, A3 is the formula for figuring their eligibility. I need A3 to say "Free", or "Reduced", or "Paid" as the end result of the data.
i have a range of numbers, 800 000 from 2 600 000 but i need to get them into numerical order packs, so i might end up with 100's of packs due to the ranges of numbers.
Need all numbers in between the ranges set. So if I input for example 8765 then the cell still comes up with 18.
IF Cell E25 is 0-7200, then 12, If cell E25 is 7201-14400, then 18, if cell E25 is 14401 - 21600, then 22, if cell E25 is 21601-28800, then 28, if cell E25 is 28801 - 36000, then 34.
I have 50,000 to 150,000 rows of data. Row M has data from 0001 to 9999. I need to delete all rows except the rows with values 2500 to 4999. I had this working with a similar and smaller spreadsheet awhile back using an advanced filter. In this case I need to delete the unneeded rows. I would imagine I could also achieve it with a VLOOKUP array and a lot of finagling but with this many records to repetitively
The two boxes up top will be my start and stop numbers. I want to generate all the numbers between (and including) the 'start' and 'stop' numbers in the top two boxes above using VBA and list them in the larger text box below (I've changed the background of the textbox to appear as it was a label, fyi).
