Formula

Question

I need to find the formula for anniversary years. Say the league started in 1980, I need my next column to show me the updated amount of anniversary years.

Answer

Not clear what the question is - are you looking for the number of years between two dates? If so, the quickest formula is probably the YEARFRAC one which returns a decimal fraction of time between two dates (so one and a half years returns 1.5)

=yearfrac(StartDate,EndDate)

In older versions ofthis would need the analyis toolpak to be installed (tools, add-ins), and you PROBABLY want to only show the whole number part, so

=Int(yearfrac(StartDate,EndDate))

would give you this.

Question

I am tring to add time. Example; 12:48 a.m 1:24 a.m. answer, 26 minutes

What is the formula to get 26 minutes with manually adding.

Answer

Answer should be 36 minutes. Whenever you are typing Time value don't type the am and pm as a.m and p.m like that type it as am and pm ok. That is you should type it as 12:48 and type a space and type am. 12:48 am or 12:48 Pm like that.

Select the Column A and format it as Time.

In A1 cell type the below 12:48 am

Copy the below formula and paste it in A2 cell =TIME(HOUR(A1),MINUTE(A1)+36,SECOND(A1))

OR

=IF(OR(A1="",ISERROR(TIME(HOUR(A1),MINUTE(A1)+36,SECOND(A1)))),"",TIME(HOUR(A1),MINUTE(A1)+36,SECOND(A1)))

Place the cursor in cell A2 and extend the selection below upto a10 by holding shift+down arrow and press Cntrl+D now the time will be applied by adding 36 minutes in every cell based on the above cell time.

Hope it's clear to you!

Question

I would like to return a value (in this case a L or W) to a specific cell based upon the background color in another cell. I can do it pretty simply with an IF statement if it were data but it is color. Is there a simple solution?

Answer

benjamin,

There is no built in function that will read the background color of a cell. Such a function can be crafted in VBA and then used like a regular worksheet function.

This would be fairly simple as far as VBA is concerned - but you may not want to use VBA in your workbook because of security warnings and so forth.

so you could put this in a general module in the VBE

Alt+F11 to get to the visual basic editor (vBE) in the Menu there, Insert=>Module.

this gives you a general module.

Put in code like this

Public Function ColorIndex(r As Range) Application.Volatile ColorIndex = r(1).Interior.ColorIndex End Function

now do Alt+F11 to get back to Excel.

in a cell put in =Colorindex(A1)

now change the background color of A1 and hit F9 to force a calculation. (changing the background color does not causeto recalculate)

-4142 would indicate that the cell has no background color (it has no fill)

otherwise, you should get a number between 1 and 56. in Excel 2007 and later,can handle a million colors in the cell background. So many colors can be mapped to the same color index.

If you need more information on this, let me know.

If the color in the background is caused by conditional formatting, then even VBA has no support for reading that color. It is best to use the conditions/formula specified in the conditional formatting rather than try to read the color.

Question

I have a formula which i am really struggling with but I think im thinking too hard about it

2010 (sheet name) Col 1 - Invoice number col 2 - project 3 - msn 4-fee type 5 - amount invoice 6 - issued 7- funds due 8 - funds recieved 9 - dvb 10 - aam 11 - aam in $ 12 - status

(the status feild is either "pending","paid" or "overdue")

I want a second sheet named "over due" that looks into "2010" and displays each invoice that has "overdue" written in the "Status" field

In the "overdue" sheet i want it only to show: Col 1 - invoice number col 2 - project col 4 - Fee type col 5 - amount invoiced col 7 - funds due

many Robert

Answer

in sheet "over due", cell I2 (leave I1 blank; and when done, hide column I): =MATCH("overdue",OFFSET('2010'!$L$1,I1,0,1000,1),0)+I1 and fill down quite far Hide it so the #N/A's don't show in cell A2: =IF(ISNA(I2),"",INDEX('2010'!A:A,$I2)) B2: =IF(ISNA(I2),"",INDEX('2010'!B:B,$I2)) C2: =IF(ISNA(I2),"",INDEX('2010'!D:D,$I2)) D2: =IF(ISNA(I2),"",INDEX('2010'!E:E,$I2)) E2: =IF(ISNA(I2),"",INDEX('2010'!G:G,$I2)) fill A2:E2 down as far as you want. Row 1 can contain the headers.

HTH

Question

Good afternoon.I hope that my question makes sense. This is the info that I have.

Name DOHire Check Dates hours used John Doe 06/13/2007 08/15/2007 15 John Doe 06/13/2007 02/10/2008 12 John Doe 06/13/2007 09/21/2008 06 Jane Doe 08/10/1992 08/21/2007 20 Jane Doe 08/10/1992 11/02/2007 05 Jane Doe 08/10/1992 09/25/2008 16 I want to write a formula that will look at the names, then go and look under the check dates and give me totals of hours. (Ex. on the 2nd sheet it will have a colum that says Vacation hrs used between 01/01/2007 andAnniv. Date. or fromAnniv. to end of yr.) I hope this makes sense

Answer

Jordon

I don't have quite enough info to give you an exact formula but I will suggest what you may do. I believe, based on what you have given me, you can use the sumif formula

Assuming your data is in columns A thru D and in rows 2 thru row 100.

The formula might look like this =+SUMIF(A2:D100,"john doe",D2:D100)

The formula says go to the range A2 thru D100, and if column A contains John Doe, then add up the amounts from column D. Using the data from your example the result would be 33 (15+12+6).

Hope this is what you needed

Question

QUESTION: I have a database that includes a person's city of residence and date they had an appointment. I want to count how many appointments there were in one city per month. So I guess I'm wanting my formula to say look in column b if this columns says "Toronto" then look in column c and add up how many occurrences there are during December 2009. Hope that makes sense. Heres an example of my database:

   A               B         

2010-01-07 Toronto 2010-01-08 Ottawa 2010-01-07 North Bay 2010-01-07 Toronto 2010-01-07 Ottawa 2010-01-04 Toronto

Ultimately I would like to be able to do this for each month and for each city.

ANSWER: Let me presume - in C2 you enter desired month (say 1) in D2 you enter desired city (Say Toronto) Then to get count of Toronto in Month 01, you enter following formula in E2 =SUMPRODUCT((MONTH(A:A)=C2)*(B:B=D2))

---------- FOLLOW-UP ----------

QUESTION: yes that is correct. Now I realize I have forgotten the third part to my question. So if i were to keep all that but add in another condition to be met. For example In column C There will be one of three conditions a 1, 2, or "blank" cell will follow. So If i were to say I want to know how many Rows contain Toronto, Date in question, and either a 1 or a 2. How do I enter that ?

So it looks As follows:

A B C 2010-01-07 Toronto 1 2010-01-08 Ottawa 2 2010-01-07 North Bay 1 2010-01-07 Toronto 2010-01-07 Ottawa 2 2010-01-04 Toronto 2

So my ocunt would say there are 2 appointments in Toronto during the month od January 2010?.

Answer

Just try taking sumproduct of three entities.... in D2 you enter desired month (say 1) in E2 you enter desired city (Say Toronto) in F2 you enter desired Code (Say 1)

Then to get count of Toronto in Month 01, you enter following formula in G2 =SUMPRODUCT((MONTH(A:A)=D2)*(B:B=E2)*(c:c=F2)

Question

QUESTION: I've been trying to write a formula that checks Column A for a(Match) then checks Column B for a second(Finalised) and sums all these as a total e.g.

Column A Column B Match Finalised Expire Finalised Match Diarised

If I was looking to see how many "Match" were "Finalised" this should return a value of 1.

Can you help?

ANSWER: =sumproduct(N(A1:A100="Match"),N(B1:B100="Finalised"))

---------- FOLLOW-UP ----------

QUESTION: Thanks for this. I have created a separate table to show the data on the same sheet and copied the formula onto it but it is returning "0". Any ideas?

Answer

send me the wb - bobumlas@yahoo.com - use subject of "AllExpertsQ"

Question

i have onefile with 50 sheets.these sheets are same format means if the sheet1 cell C10 contains the Shope name then all the 50 sheets contains different shope name in C10 cell.im trying to make one report based on this file.i need column A as Shope name,.....im using this formula (=Sheet1!$C$10).when i m draging down then it should change sheet number means next cell it will show (=Sheet2!$C$10).is it possible???? can u help me.... thanks bindhya

Answer

You can use the INDIRECT formula and a helper columns for this. In the helper column type "Sheet1", and then drag it down so it becomes Sheet2 to Sheet50. Now, next to each cell on that range you can use a formula like: =INDIRECT(A1&"!$C$10) This should give you the Shope number from the corresponding sheet.

Question

In column A I have a student Id#. In column B I have a semster 1 and/or 2. So column A should have the same student ID# twice (once for semster 1 and once for semster 2). In column C is the Homeroom teacher name (or code). Is there a way to find if a student in column A has two different Homeroom teachers in column C? They should look like this (except student 232 has a different homeroom teacher for semester 2). Is there a formula for finding these students? A B C 222 1 704 222 2 704 423 1 708 423 2 708 232 1 801 232 2 805

Answer

Sure. You could use the COUNTIF function to determine how many homeroom teachers each student has.

You can use the SUMIF and COUNTIF functions to add up or count values based on specific criteria, respectively.

Here is a free tutorial that explains how to use both:

http://www.599cd.com/tips/excel/sumif-countif?key=AllExperts

Question

I need assistance on anequation that applies to a Hotel.

There are 3 columns. Column A is the resort #. Column B is the room code. Column C is the room status.

I am looking for an equation that shows:

Given any two rows, if Column A is 5 for both rows, Column B is 15 for the top row and 14 for the botand Column C is 1 for both rows, than the answer would be 1.

It is hard to explain so please see attached and let me know if you have any questions. I really appreciate the assistance.

Brian

Answer

the only way I get it is if you include the following formula con cell D2 =IF(AND(C2=C1,A2=A1,B1=15,B2=14),C2,0)

Drag it over the rest of the cells

and then this formula on cell J16 =SUMIF(A:A,10,D:D)

and change it depending on the resort #

Question

QUESTION: I am putting together a time sheet. This will log time spent per project. Since the start time can be a higher or lower number than the end time the formula must accommodate that change so that the answer doesn't change from a positive # to a negative #.

ANSWER: Glad to help. You can set up an absolute number formula. Let's say you have your dates on cells A1 and B1. Then you formula will be: =ABS(A1-B1).

---------- FOLLOW-UP ----------

QUESTION: Used your suggestion but the answer came our wrong. Here is my document. TOTAL START TIME END TIME HOURS 9 11 2.00 11 1 10.00

11 O'clock to 1 o'clock whould be 2 hrs. not 10.

Answer

I'm sorry. I misunderstood your question the first time.

Now. I can advice yoo to use dates and time formats such as dd/mm/yy hh:mm am/pm and then you'll never get a wrong result.

Still, If you want to go ahead with your current format you can use a conditional formula such like this

=IF((B2-A2)<0,12+(B2-A2),B2-A2)

It'll work with your example. But the problem is its limited to <12 hour projects

Question

In my spreadsheet i mark off who i paid by highlighting the cell (payment amount). Is there a formula that can check which cells are highlighted and then totals that amount and then minuses it off another total. I would like to see how much i still need to payout. Also, is there a formula that looks at a cell value if the value is zero it would automatically hide the entire row.

Answer

Excel cannot easily do math using colors. You are MUCH better off by using an additional column and putting -say- a 1 in that column on the same row as the payment amount you have paid. Then you can use SUMIF to calculate what has been paid and what not, or use autofilter to filter your list in paid or unpaid records.

Question

In column A I have a list of numbers. In column B I would like a specific code to appear based on the data in column A. E.g if the number in column A1 is between 9 - 16 I would like to produce the symbol 3H to appear in column B10.

I am not sure if their is a formula that will do that for me. Any ideas.

Answer

Chris

Try this formula

=IF(AND(A1>=9,A1<=16),"3H","")

The formula says

If cell A1 is greater than or equal to 9, and if cell A1 is less than or equal to 16 the put a

3H in the cell, otherwise put nothing in the cell.

Question

In column A I have a list of numbers. In column B I would like a specific code to appear based on the data in column A. E.g if the number in column A1 is between 9 - 16 I would like to produce the symbol 3H to appear in column B10.

I am not sure if their is a formula that will do that for me. Any ideas.

Answer

Chris

=if(A1<9,"AB",if(A1<17,"3H",if(A1<25,"Z9","Q7")))

would be one way. A lot will depend on how many different codes you have and corresponding number ranges.

If there are a lot of them then you can make a separate "table" in another sheet

1 AB 2 AB 3 AB 4 AB 5 AB 6 AB 7 AB 8 AB 9 3H 10 3H 11 3H 12 3H 13 3H etc

assume the above is in sheet2!A:B (columns A and B)

in B1 of the original sheet

=vlookup(A1,Sheet2!A:B,2,False)

Of course you could use the version of Vlookup that depends on a sorted list. That would require significantly fewer entries (if you have large number ranges) and be able to handle non integer numbers.

There are probably other ways as well, but that is about what I can tell you based solely on the very limited information provided.

Question

In column A I have a list of numbers. In column B I would like a specific code to appear based on the data in column A. E.g if the number in column A1 is between 9 - 16 I would like to produce the symbol 3H to appear in column B10.

I am not sure if their is a formula that will do that for me. Any ideas.

Thanks Chris

Answer

Put this formula in B10 cell. =IF(AND(A1>=9,A1<=16),"3H","") If the number in cell A1 is between 9 - 16 then it will result 3H in B10 cell otherwise it will result blank.

If you want to show "Not Matching" instead of blank then use this formula in B10 cell. =IF(AND(A1>=9,A1<=16),"3H","Not Matching")

Question

Ok i think i'm with you

Is the below possible then?

Sales: <1000 1000-2000 >2000 Units <20 0 0 0 20 - 30 0 .15 .15 >30 0 .15 .20

Sorry had to do a new question as it wouldn't let me do anymore replies

Dan

Answer

Dan,

we can handle the whole first row and first column with

or(units<20,sales<1000)

and the highest bonus with And(units>30,sales>30)

which leaves everything else at a bonus of .15. The formula for that is:

=if(or(units<20,sales<1000),0,if(And(Units>30,Sales>2000),.20,.15))

Question

QUESTION: Hello there,

I've been trying for some time to get this IF formula to work but to no success, please can you help:

1st Target (Sales) Less than ?1000 = No Bonus between ?1000 and ?2000 = 15% bonus more than ?2000 = 20% bonus

Ok thats the easy part

2nd Target (Units) More than or equal to 20

Ok the formula needs to check two cells and if in one of them the units are 20 or more and in the other the spend is ?1000 or more then the sales bonus structure kicks in.

Fingers crossed you can help

I look forward to your reply

Many thanks Dan

ANSWER: Dan

First formula

=if(sales<1000,0,if(sales<=2000,.15*sales,.20*sales))

=if(units>=20,firstformula,0)

so

=if(units>=20,if(sales<1000,0,if(sales<=2000,.15*sales,.20*sales)),0)

---------- FOLLOW-UP ----------

QUESTION: Thank you for your reply thats worked great, one more question if you don't mind

i would like to add a second stage to the unit target:

If units below 20 or sales below ?1000 then no bonus if spend between ?1000 & ?2000 and units between 20 & 30 15% bonus if spend over ?2000 and units over ?30 then 20% bonus

is this possible?

Many thanks Dan

ANSWER: Dan,

OK, but you leave holes in your analysis. Spend >2000, but units between 20 and 30 = no bonus or you have not defined or allowed for a bonus

=IF(OR(Sales<1000,Units<20),0,IF(AND(Sales>=1000,Sales<=2000,Units>=20,Units<=30),0.15,IF(AND(Sales>2000,Units>30),0.2,0)))

---------- FOLLOW-UP ----------

QUESTION: Ah yes so i have

I would like it to show the 15% bonus if they do not meet both criteria to achieve the 20% (?2000 & 30units)

would that be the same as what you have given me?

thanks Dan

Answer

Dan,

No, because you have two dimensions. In a normal single dimension IF construct, you can progress across the the scale and if you meet a criteria, you know you are there because you have failed to meet lower criteria.

=if(sales<1000,0,if(sales<=2000,.15,.20)) so if I had sales less than 1000, then I get no bonus and the function is done If greater than 1000, then I move to the next IF statement knowing that my sales were greater than 1000 and I don't need to check that again. So if less than 2000, then I know it is between 1000 and less than or equal to 2000. If I fail there, then I know I am greater than 2000. That is not true for the two dimension test, so you will need to check each cell of possibility

sales: <1000 1000-2000 >2000 Units <20 0 0 0 20 - 30 0 .15 .15 <== newly added - was zero >30 0 0 .20

this is where you are right now:

=IF(OR(Sales<1000,Units<20),0,IF(AND(Sales>=1000,Sales<=2000,Units>=20,Units<=30),0.15,IF(Sales>2000,IF(Units>30,0.2,0.15),0)))

Question

I have following problem: there is a table with following formula in space for month dates: =(IF(DATE($B$4,$D$4,COLUMN()-1)<=MONATSENDE(DATE($B$4,$D$4,1),0),TEXT(DATE($B$4,$D$4,COLUMN()-1),"T")," ")) However, when I type dates in the box, the numbers are not there, but the same sign as before ### Could you please assist me on this one? Thank you, really appreciated. Rgds,

Answer

When you see ###### in a cell in Excel, that usually means that the COLUMN is not WIDE enough to show all of the data. Try increasing the width of the column and see if that helps. As far as your formula goes, I really couldn't test it without seeing your sheet.

Question

YOU POSTED THIS FORMULA AT LINK http://en.allexperts.com/q/Excel-1059/combinations-numbers-1.htm

IT WORKS GREAT FOR 3 SPACE RESULT FOR NUMBERS AND LETTERS! HOW CAN THE FORMULA BE ADJUSTED FOR 4 SPACES NUMBERS AND LETTERS?

THANK YOU VERY MUCH!!

Scott

Answer

Scott, There are basically 3 parts of the existing equation. The first part divides the row number by 36^2 or 1296. The second part divides the row by 36^1 (36) and the last part doesn't specify that a division occurs, but essentially it divides the row by 36^0 or 1. To add a fourth character, just follow the pattern and precede the first three parts with 1 that divides to row by 36^3 or 46656: =CHAR(INDIRECT("A"&MOD(INT((ROW()-1)/46656),36)+1))&CHAR(INDIRECT("A"&MOD(INT((ROW()-1)/1296),36)+1))&CHAR(INDIRECT("A"&MOD(INT((ROW()-1)/36),36)+1))&CHAR(INDIRECT("A"&MOD(ROW()-1,36)+1))

The problem that you will run into is that 4 characters will give 1,679,616 possible combinations. The latest version ofonly has 1,048,576 rows, so the highest combination shown will be MH33. Previous versions ofonly have 65,536 rows so the highest combination shown will be 1EKF.

If you need to show all of the possible combinations on one worksheet put the following formula in Cell B1, then copy it and paste it in the range B1:AK46656: =CHAR(INDIRECT("A"&MOD(INT((COLUMN()-2)),36)+1))&CHAR(INDIRECT("A"&MOD(INT((ROW()-1)/1296),36)+1))&CHAR(INDIRECT("A"&MOD(INT((ROW()-1)/36),36)+1))&CHAR(INDIRECT("A"&MOD(ROW()-1,36)+1))

Best Chad Welch

Question

 I am trying to write a formula that will count the number of times a certain text appears in a list between two dates.  For example, the number of times that "MS Level 1" appears in Column I between the dates of 12/1/09 and 12/31/09.  The dates for each record are located in column E.  
I would appreciate any help!!

Kevin

Answer

=SUMPRODUCT((E1:E1000>=DATE(2009,12,1))*(E1:E1000<=DATE(2009,12,31))*--(I1:I1000="MS Level 1"))

OR

=SUMPRODUCT((E1:E1000>=DATE(2009,12,1))*(E1:E1000<=DATE(2009,12,31)),--(I1:I1000="MS Level 1"))

Extend the selection from E1:E1000 & I1:I1000 to your desired range.

If you are using2003 then DON'T refer the whole column in the above formula

If you are using2007 or 2010 then you can refer the whole column in your formula like the below:- =SUMPRODUCT((E:E>=DATE(2009,12,1))*(E:E<=DATE(2009,12,31)),--(I:I="MS Level 1")) OR =SUMPRODUCT((E:E>=DATE(2009,12,1))*(E:E<=DATE(2009,12,31))*--(I:I="MS Level 1"))