Over 1 million tech questions and answers.

Excel 2013 - how to keep a formula consisten

Q: Excel 2013 - how to keep a formula consisten

Hi

I am trying to tie one cell into a formula that is being pasted into multiple rows.
I know you can use $ to keep the cell consistent but it doesn't seem to be working.

So if the value is in c9 and then first row gets c9 then the new row gets c10 etc... I need it to be c9 throughout the column.

RELEVANCY SCORE 200
Preferred Solution: Excel 2013 - how to keep a formula consisten

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Excel 2013 - how to keep a formula consisten

You use $C9 to keep the column consistent, C$9 to keep the row consistent and $C$9 to keep the cell consistent. Are you doing that and it's not working?

Read other 2 answers
RELEVANCY SCORE 60.8

For some reason I cannot get Excel 2000 to display the results of a formula. Only the formula text displays. I've gone to Tools-Options-View-Windows Options and checked and unchecked the Formulas checkbox. It doesn't make any difference.

Help!

Thanks,

Thomas
 

A:Excel 2000 Formula Text Displays instead of Formula Results

Format the cell as a number, it prolly is fmt'd as text
 

Read other 3 answers
RELEVANCY SCORE 60.4

Hi ~

I'm working on a financial formula, trying to calculate a growth rate for a row of cells. The problem is that not all rows have complete data sets, so the formula picks up blank cells and returns a #DIV/0! error.

The existing formula works fine for those rows that have non-zero entries in all cells, and I *can* manually adjust the formula for the starting / ending cells in each row--but what a pain(!). As this spreadsheet grows larger, or the data sets change over time, it means I have to constantly keep adjusting it for every row of data involved, which is extremely time-consuming.

Here's an short example of what I might have in any given row:

A1 (blank), B1(blank), C1(blank), D1(.10), E1(.25), F1(.72), G1(1.0), H1(.80), I1(.60), J1(.75)--then in K1 is the growth formula, e.g. =((J1/A1)^(1/10)-1).

Is there ANY way to create a formula that will dynamically adjust for the blank cells that might exist across a row of data and let the formula do the calculation?

I'm new to advanced Excel topics, but any ideas would be MOST appreciated(!).

Thanks -

CP
 

A:Excel Formula / How Can I Ignore Blank Cells In Row Formula?

Read other 14 answers
RELEVANCY SCORE 59.2

If it's possible to do this, what is the formula syntax for an if/then formula in one sheet that refers to the cell value in another sheet in the same workbook?
For example in Sheet 2, the formula would be something like, if Cell A10 in Sheet 1 is greater than 0, then the value of Cell B20 in Sheet 2 would by "Yes" but it would be "No" if the valuein Cell A10 in Sheet 1 was less than 0.
 

A:Excel formula using formula between sheets in workbook

Read other 6 answers
RELEVANCY SCORE 59.2

Hi, I need to do the following:

Search array
1 a 12 asdas
2 b 121 agrsfsa
3 c 3 qwgds
4 b 131 erwer
5 e 4123 q3eqt

need to return something like this
lookup value b

answer should look like this

2 b 121 agrsfsa
4 b 131 erwer

index, match can give me one row, however what could return multiple values

Thanks in advance for your help.
Cheers
 

A:Excel: Is there any formula that would do Autofilter's functionality as a formula

Read other 8 answers
RELEVANCY SCORE 56.8

I have created a formula in Excel that works fine, but when I put it into a MS Word table, it doesn't seem to work. The formula adds up a column of prices when the resulting column has a specific word in.

The formula is:
=SUMIF(E:E, "Yes", D:D)

Example:


Is this possible to do? If so can anyone help converting this so it will work in MS Word.

Thanks, Adam.

A:Excel Formula to Word Formula

You'll have to do this:

https://support.office.com/en-us/art...4-0d7e88062257

Read other 2 answers
RELEVANCY SCORE 54

Hi,

I am trying to pull a dashboard view from a "Tracker". Given below is the formula that I used:

=COUNTIFS('KT Tracker'!$T$2:$T$138,$C$2,'Tracker'!$U$2:$U$138,"<="&TODAY()+7,'Tracker'!$L$2:$L$138,'Monthly Dashboard'!$B17,'Tracker'!$AF$2:$AF$138,'Monthly Dashboard'!$A$13,'Tracker'!$AD$2:$AD$138,"<>Cancelled")

This perfectly works fine in my laptop that has excel 2013 but when I email this to my team who have Excel 2010, they only see zeros. We have checked all options like enabling Automatic Calulcation of Formula, removing all named ranges in the sheet etc., but it is just not working.

Can you please help ? This is a bit urgent and I need to present this for a meeting tomorrow.
Appreciate your help and thanks in advance !!!
 

A:COUNTIFS working in Excel 2013 but not in Excel 2010

It might be something to do with "<>cancelled"
 

Read other 1 answers
RELEVANCY SCORE 54

I cannot search for data in excel 2013. This is new issue. I do notice that in the find window there is an entry for Preview* in the box next to format in the options window that I have never seen before. I get the message cannot find what you are looking for but I know it is there because I am looking at it.

This is new issue. Worked until last night when I was doing a sort and had to make all merged cells uniform in size. Now I can sort but cannot find.

Have closed and reopened excel, but it did not correct problem.
 

A:Solved: Excel search does not work Excel 2013

Read other 7 answers
RELEVANCY SCORE 50.4

i need a bit more help with my time sheet

i have posted it here: http://www.ijnw27094.pwp.blueyonder.co.uk/wagesmast.xls

the problem that i am now having is that if some one is at work on an overtime shift i need to show the full time on shift as over time, i have inserted another col that if i put ot in ther what is the if statement that i will use to get it to just show in the overtime col and not in total hours, or can i use an or statement as in if(c4="agy",if(c4="ot", and then what else would i need to do

TIA
 

Read other answers
RELEVANCY SCORE 50.4

If I have a whole column of numbers -many many numbers I have them sorted in asending order now I want to pull the duplicates. How can I do this.
 

A:Excel formula help

Read other 6 answers
RELEVANCY SCORE 50.4

I am doing a spreadsheet for a volunteer fire department and we want to be able to keep track of the percentage of meetings, drills, and fire calls each person attends. I thought I had it right but I guess not now. I've included what I have done so far.
My problem is I don't know how many members there is at the time so I just created enough for 47 members to be tracked (I'm sure it's less than that). I realize I have an error formula but after input is placed in cell it disapears. I do not want that error to show up if I haven't filled the cell (I don't want error printing out on sheet if there is no member listed for that row).
Also I would like someone to verify that I did things right at the bottom... Where I have Department Averages.
This is like my first spreadsheet I've done so be easy on me. Trial and error. Any suggestions would be appricated.
Thanks in advance,
Art
 

A:Excel formula help please...

Filter the divisor with an IF function,

=IF(B6="","",C6/B6)
 

Read other 3 answers
RELEVANCY SCORE 50.4

This may seem a silly question but can anyone tell me where the equals sign has gone on the Formula Bar that you used to see with Office 97???
 

A:Formula Bar MS Excel (XP)

Looks like MS deemed it unnecessary. The = sign button was "Edit Formula".

It's been replaced by the Insert Function (formerly known as Paste Function) button. If you want to 'edit a formula', you would just use the Insert Function button.
Additionally, when clicking the Insert Function button, a prefixing = sign automatically appears, defining it as a formula.

If you really wanted, you could add an = symbol to one of your toolbars - right-click toolbar area, Customize, on the Commands tab select Insert in the Categories pane and "=" Sign in the right pane...

Gram
 

Read other 1 answers
RELEVANCY SCORE 50.4

I am looking to create a spreadsheet that calculates vacation accrual and takes into account Flex Days, etc. Vacation accrual is accumulated at 6% of someones gross pay so at the end of 52 weeks, it would add up to their alotted vacation. I.E. I get 3 weeks a year. Not everyone gets Flex Days so I would only calculate this for those that get them.

Are you able to assist me in coming up with the proper formula?
 

A:Excel Formula Help

Read other 8 answers
RELEVANCY SCORE 50.4

need help with a formula... I want the results to be in column AA

If V6 is blank I want the text in aa to read Booking? , if V6 has a date in it I want the text in aa to read Sailed? , if B1-V6>4 I want the text in aa to read Sailed??, if B1-V6>7 I want the text in aa to read Documents?
I have been trying to get this to work and I am not sure if it can be done

Thanks
Chris
 

A:excel formula help

Read other 7 answers
RELEVANCY SCORE 50.4

Is there a way I can make a formula in Excel so that it will only count certain combo's of numbers that up to certain numbers?

for instance... im working on a project for a statistics class and I have to make a game with payouts and such.

I have generated two columns of random numbers 1-6
It looks like this: (the numbers represent colors, 1=red, 2=orange etc)

Color 1 Color 2 Total Win/Lose
2 6 8 Win
4 4 8 Win
5 2 7 Lose
6 3 9 Lose

I have the formula for the Win/Lose column set up so that if the number in the total column is 4,6, or 8 it will say "win" but, i only wanted to count 1+3, and 3+1 for the number 4, but as we all know 2+2=4 aswell and I cant have that.

So I need help making my excel sheet only recognizing the follwing as a "Win":
1+3 =4
3+1 =4
1+5 =6
5+1 =6
3+5 =8
5+3 =8

I attached a sample of my work.
 

A:Need help with excel formula

Read other 9 answers
RELEVANCY SCORE 50.4

What I am looking for is a certain number of hours would = a specific number.
So if you had 1350 hrs, that would =2. If you had less than 1350 but greater than 674, then it would =1. If you have less than 674 than it would =0.
 

A:Excel Formula Help

Read other 16 answers
RELEVANCY SCORE 50.4

I am trying to create a formula in excel with great difficulty.
The files has 3 tabs. I am trying to take from the first tab called tracking. Here is what I need:

count if d3:d999="Ace" and e3:e999="Damage"

Now, it won't seem to do both functions. I only need the count if both are true within that range, so if D5="Ace" and E5 ="Damage" then count 1, but if both aren't true for the range, then not to count.

Any help?
 

A:Excel Formula Help

irishgal said:

I am trying to create a formula in excel with great difficulty.
The files has 3 tabs. I am trying to take from the first tab called tracking. Here is what I need:

count if d3:d999="Ace" and e3:e999="Damage"

Now, it won't seem to do both functions. I only need the count if both are true within that range, so if D5="Ace" and E5 ="Damage" then count 1, but if both aren't true for the range, then not to count.

Any help?Click to expand...

Try,

=SUMPRODUCT(--(D3 : D999="Ace"),--(E3:E999="Damage"))
 

Read other 2 answers
RELEVANCY SCORE 50.4

Need some help on an excel for mac formula. I am trying to make a cost analysis spreadsheet and do not know how to make a formula for the following information:

overnight fee is 240.00 per night

1 to 5 nights with a minimum purchase of 800 gallons-no fee (799 or less-full fee/per night)

6 to 12 nights with a minimum purchase of 1000 to 1500 gallons-half the fee, 1501 or above-no fee (less than 1000- full fee/per night)

12 nights or over with minimum purchase of 1500 to 1999 gallons-half fee, 2000 or more- no fee (less than 1500-full fee/per night)

So far I can get the first one, =IF(AND(B4<=5,E4<800),B2*B4,"0") but after that I am stuck. B2 is the $240.00, B4 is the number of nights and E4 is number of gallons purchased. If there is a better way, I am all ears.

Please make any directions as step by step as possible, as I know very little about excel! Thanks in advance!
 

A:excel for mac formula HELP!!!!

Read other 6 answers
RELEVANCY SCORE 50.4

I am having trouble with working out how to calculate vat @ 17.5% in excel.
I need to work out the vat inclusive price.
can anyone help.
 

A:Excel vat formula

Not sure exactly what you want, but hope this helps:

If £15.00 if the amount including VAT and you want to find what the net amount is (ie excluding VAT) then divide the amount by 117.5%:

+15/117.5% or put in sparate cells and create formula eg +A1/B1

If you want to find how much VAT is on £15.00 then multiple the amount by 117.5%:

+15*117.5% (or in cells would be +A1*B1)

Is this what you wanted?
 

Read other 2 answers
RELEVANCY SCORE 50.4

I'm working on a long formula that starts with "if" - I can't get it to work -
thanks
 

A:Help with an Excel formula

Read other 6 answers
RELEVANCY SCORE 50.4

Hi,

I have a table of text values, case sensitive and I tried using COUNTIF() to count the number of occurrences of a particular pair of letters, like so:

BB:RR
BB:Rr
BB:rr
Bb:RR
Bb:Rr

etc....

I did COUNTIF(Range,"*BB*"), but I found it's not case sensitive, and it's counting BB and bb as well. Is there any way to make it case sensitive?

Rgds,
 

A:Excel Formula Help

Read other 11 answers
RELEVANCY SCORE 50.4

I am working on an excel formula that involve if/then equations. I need to know a bit about how they work and what order or series to put my equation in to get a YES/NO answer regarding greater and less than signs. I don't get it : (
 

A:Excel formula

IMO Excel Help contains a pretty good explanation on worksheetfunction IF.

formula = IF(logical expression ; result if expression is true; result if expression is false)

What is it exactly you don't get here? Is it about how to build a logical expression? If so, then I think more input is needed from your part regarding what you want to do.
 

Read other 3 answers
RELEVANCY SCORE 50.4

Hi,

I am trying to work out how to enter a formula to show the total of a figure in a different data sheet minus 10% divided by 12.

Can you please help?

I can get as far as ='Part A Data'!B10*(1-10%) but do not know how to show this answer divided by 12

Thanks!
 

Read other answers
RELEVANCY SCORE 50.4

Cells A1:A10 contains a list of leap years
Cell B1 is an input cell
Cell C1 contains the formula.

Formula = when a value is entered in cell B1, check if that value is in the range A1:A10. If it is, return a number value of 366. If not, return a number value of 365.

Thanks,
T

Read other answers
RELEVANCY SCORE 50.4

Hi
I have this excel sheet with several tabs one for each week (1,2,3 etc)

Each week I copy last week's tab and rename it with this week's number. My problem is that I have some formulas in the sheet that takes one cell and subtract from the same cell the week before e.g

=A1-2!A1 (A1 being week 3 and subtract with 2!A1 being week 2)

But when I copy the sheet from last week (week 3) I this formula will be wrong since I need it to be:
=A1-3!A1

Is it any way that I can set up the formula without having to go into all the formulas and change it from 3! to 4!?

I hope this was understandable!

Thanks!
 

A:Excel formula help

Re: "having to go into all the formulas" -- have you tried Replace (CTRL+H) to replace 3! on the new sheet with 4!?

HTH

(EDIT: welcome to the board)
 

Read other 1 answers
RELEVANCY SCORE 50.4

hi,
i was trying to find a solution for a formula in excel.

i have a list of people, each one has a specific date.

i want to count by month the number of people on that list

the result could be on a graphic or cells.

thanx
 

A:help with excel formula

Read other 6 answers
RELEVANCY SCORE 50.4

Hi guys/gals,

I'm looking for the formula on my spreadsheet to give out a value and if there is no value, display N/A as the output. So for example =E2-D2 is already in the cell and gives me what i want however when there is no value in E2 i would like it to display N/A instead of #Value. The 2 cells for the formula are set up as date/time but occassionally letters may be in these cells instead. Ive probably made that sound complicated. Excel newbie sorry.

Thanks in advance for any replies.
 

A:Excel formula

The following formula for exactly what you requested uses the ISBLANK function as the logical_test parameter in an IF function.

Code:
=IF(ISBLANK(E2),"N/A",E2-D2)
A more general approach uses the ISERROR function to return "N/A" if any kind of error is found for E2-D2.

Code:
=IF(ISERROR(E2-D2),"N/A",E2-D2)

 

Read other 1 answers
RELEVANCY SCORE 50.4

My question is how do I input a formula into Office 2010 Excel that calculates set commission amounts ($100,$150,$200,&$250) for various dollar sales amounts?
I have been able to work it this far:
=IF(D4>2999,D4<4000)*("$200.00")
The part I need to know is how do I expand this formula to include the next level of commission pay off $250.00
How close am I?
=IF(D4>2999,D4<4000)*("$200.00") (D4>=4000)*("$250.00")
I am not sure how I join or add the brackets together.
I appreciate you help.
 

A:Excel Formula

use nested IFs

so

IF( test , true , false )

now to nest in the false section (you can also nest in true)

IF( test, true, IF(test, true, false) )

if you want to give the details , i can set up

=IF(D4>2999,D4<4000)*("$200.00")Click to expand...

not sure why thats works

I would also look at the structure of the data and you may avoid using the between

but for between use

AND( D4>2999,D4<4000)
as the test

but you can often start with the greatest value

IF( D4 > 4000, "4001+ commision calc", if(d4> 2999, "3000+ commision calc", if( d4>1000, "1001+ commision calc", "value for below or equal 1000")

if you want to include greater or =
>=

the tests are carried out in the order written
so if its false for >4000 - then the next test shows below 4001 but greater than 2999
 

Read other 3 answers
RELEVANCY SCORE 50.4

I hope you can help me with a fairly simple formula, but if formulas are really hard for me.
I want to put the work PAID in Cell b if Cell c has a date in it, otherwise I want it blank. Thanks in advance for your help.
 

A:Excel Formula Help

Read other 6 answers
RELEVANCY SCORE 50.4

Hi,

Can someone please tell me how this formula works:

{=SUM(($F$74:$F$123)*($M$74:$M$123>=G135)*($W$74:$W$123>=G135))/4*(1+Data!$AI$23)^(G135/4)}

All the data is numerical and the result that I get from the formula is simply ($F$74:$F$123)/4.

Thanks.
 

A:Excel formula

First, you should be getting a numerical result, so click in the cell, go to Format, Cell and select General. Other formats may work, but apply them later.
Next, the {} means this is an array formula (some people call them CSE formulas - you will see why soon). You don't type the {} in yourself - you type in the formula and hold down Control, Shift and hit Enter (hence CSE) and Excel adds the brackets automatically.
Next, you have several ranges, such as $F$74:$F$123. Just for clarity, I will point out that the range could be written as F74:F123 but that would be a relative range - if you copied the formula and pasted it elsewhere, Excel would alter the range to match. Having the $ in the formula makes these absolute values, so no matter where the formula is pasted, the range looked at is always this one (or, rather, these ranges.)
Your formula does several things, so let me break them up.
{=SUM(($F$74:$F$123)*($M$74:$M$123>=G135)*($W$74:$W$123>=G135))/4*(1+Data!$AI$23)^(G135/4)}
Any portion like
=SUM($F$74:$F$75)>=G135 (also an array formula)
looks at the sum in the range to see if it is greater or equal to the value in G135. (If it isn't (in any of the comparisons) you get a value of 0.)
The next batch is more of the same - just algebra.
Finally, (1+Data!$AI$23)^(G135/4) takes the value on the sheet called Data in cell A23, adds 1 to it and raises the whole thing to the value of whatever is in cell after dividing that value by 4 (e.g., if the value in Da... Read more

Read other 3 answers
RELEVANCY SCORE 50.4

Attention Excel Gurus,

I had a formula once that searched through a range of text and if it found a duplicate it marked the cell 0 if it was the first or only occurence of the value it returned a 1. it looked something like =IF(a1, A1:A2 = a1,1,0) But that is not it. Does anyone know what I'm talking about?

Thanks

JHorsley
 

A:Looking for Excel Formula

Hi JHorsley,

Do you mean looking for duplicates in a column of text?

Say you have a list of Names down column A,
the following in say cell B2 copied down column B will display "Dup" or "Not Dup" alonside each name in Column A depending on if thet Name exists above it or not.

Replace "Dup" or "Not Dup" to suit .
=IF(COUNTIF(A$1:A2,A2)>1,"Dup","Not Dup")

lol
Hew

Note: no formula in B1 as there is nothing above A1.
Note as you copy the formula down col B, the range will change to include upto that row but the $1 will ensure it always starts from the top.
 

Read other 1 answers
RELEVANCY SCORE 50.4

Hi,

I am desgining a spreadsheet to calculate the cost of a process within our business.

This is how I would like the spreadsheet to function:
I have a list of functions on the left, in column B. Accross the top I have the processes that are possible for any scenario. Next to the function if the user enters a X then along the same row under the relevant processes another X is entered.

I have done this using a simple IF formula. e.g. =IF(C10="x","Required","") and just copied this formula into the cells that will need to X. (Hope that makes sense!)

Each process, even if it is needed for more than one function, will only need to be carried out once. I am looking to find a formula that will go in a cell beneath the rows of processes and if "Required" is entered anywhere in that column then a cost is entered at the bottom.

Basically a formula to detect whether anywhere in a column the word "Required" appears and enter a figure at the bottom for what the process will cost.

I have tried using formulas like =IF(C10:C20="Required","80","") - meaning if reuqired appears in any of the cells from c10 up to c20 then enter a cost of 80. Also using OR statements but both without much luck.

If anyone actually managed to follow this and suggest ant ideas id be very grateful.

Thanks in advance
 

A:Excel formula

Read other 8 answers
RELEVANCY SCORE 50.4

I need help with an Excel Formula. I am creating a gradebook using points and not values for grades. For examples, E5:BE5 represents the total points possible for each assignment, E6:BE6 represents the actual points received, and E7:BE7 represents attendance. BD7 will sum E5:BE5, BC7 will sum E6:BE6, and BC6 will give the percent grade. My problem is when a new student comes in during the middle of marking period, I only want to add the total points from the point when he/ she entered. In other words, in E7:BE7 (attendance line) I want to put an "n" for new and in BD7 I wantto have a formula that states "if there is an "n" in E7:BE7 to sum only the cells of E5:BE5 that are after the column that "n" is in". Is this possible?
 

A:EXCEL formula

lbkubic said:



I need help with an Excel Formula. I am creating a gradebook using points and not values for grades. For examples, E5:BE5 represents the total points possible for each assignment, E6:BE6 represents the actual points received, and E7:BE7 represents attendance.Click to expand...

just to be sure what you're saying; Cell E5:BE5 contains the value of maximum points and the student score is in cell E6:BE6.
 

Read other 8 answers
RELEVANCY SCORE 50.4

I attached a picture of the spreadsheet/current formula. I would like to add another formula into cell K101 so that when a value is entered into M101, K101 goes to 0.
 

Read other answers
RELEVANCY SCORE 50.4

i have a time sheet im putting together and i need help:

If cell A8 = a number other then 99 put the Letter "M" in cell E8
but if cell A8 = 99 put 99 in E8
but if cell A8 is blank put "A" in E8

thank in advance
 

A:help with excel formula

The formula you need in M8 is
=IF(E8=99,99,IF(A8="","A","M"))Click to expand...

Then copy that down.

Also if you custom format the date as
dddd d/m/yyyy Click to expand...

Excel will include the day for you automatically.
 

Read other 1 answers
RELEVANCY SCORE 50.4

I currently have a formula that takes the current day, and compares to an Order Picked date.

The formula calculates is the difference between the dates is more than 2.5, "Overdue" is returned in the cell

The problem I encounter is on Mondays and Tuesdays, where I need to change the formula manually to 4.5 and 3.5
because the Sat and Sunday are not skipped and no orders are sent as those are off days. So an order picked on Friday
would show as Overdue on Monday as it is more than 2.5 days.

Original Formula : =IF((A2+2.5)<$C$3,"Overdue",IF((A2)>$C$3,"Future",""))

The new Formula: =IF(AND($D$3=”Monday”,A2+4.5<$C$3),”Overdue”, IF(AND($D$3=”Tuesday”,A2+3.5<$C$3),”Overdue”, IF((A2+2.5)<$C$3,"Overdue",IF((A2)>$C$3,"Future",""))))

has not worked. Perhaps wrongly positioned parenthesis? It returns #NAME#

A sample spreadsheet is attached.
 

A:Excel IF AND formula and more

Read other 16 answers
RELEVANCY SCORE 50.4

Hello Sir,
Can you let me know the formula in Excel:
I have a table as below:

This table, At the column "A,Status" when i Put the word" Completed", it's will automatic put current Date to Column "B, Row 2".
But i don't know after that when tomorrow arrived,at the column "B,Row 2", change date too.

A:Excel Formula

Hi,

Use this one, in the next line
3.
"=IF(A:A="Complete",B2+1)"

With this one, when you enter "Complete" in A3, it will automatically put the date in B3.

Read other 3 answers
RELEVANCY SCORE 50.4

Ok so I have my products in column B. In column H I have the price of each product. In column P, I want to be able to assign a value to each thing in column B and then add the total per month.

Product Month Price Total Product Sales by Month
Router JAN 899
Monitor JAN 219
Monitor JAN 219
Switch FEB 699

Etc.....Is there a formula for this?

Read other answers
RELEVANCY SCORE 50.4

How do you create a formula that sums items from every second column:

JanActual Jan Budget Jan Variance and so on through each month

Then I want columns at end that sum total actual, total budget, total variance

I figures there is a faster way than manually entering each actual cell # in a sum formula, but I don't know how.
 

A:Excel formula ?

You could use sumproduct.

If you have actuals in columns a1, d1, g1, etc., budgets in b1, e1, h1, etc. and variances on c1, f1, i1, etc. then:

=SUMPRODUCT(--(MOD(COLUMN(A1:AJ1),3)=1),A1:I1) will give total actuals for a twelvemonth
=SUMPRODUCT(--(MOD(COLUMN(A1:AJ1),3)=2),A1:I1) will give total budgets, and
=SUMPRODUCT(--(MOD(COLUMN(A1:AJ1),3)=0),A1:I1) will give total variances
 

Read other 2 answers
RELEVANCY SCORE 50.4

I have a sheet that contains a list of numbers for each hour of each day, monday through sunday 1 to 24. I need a formula to calculate the average of those numbers for the full week and then I need to make a chart with those averages. Can anybody help?
 

A:excel formula

How is your spreadsheet set out?

There is a simple average formula - but it all depends on the data it is looking at
 

Read other 2 answers
RELEVANCY SCORE 50.4

I need help setting up a new formula to auto populate a field with a specific drop down list based upon an initial selection from a drop down list.....if that makes any sense.

E.G

Column A has a drop down list of 10 items (lets call them 1-10 for this), I have it set so that when any of these options is selected a 'Cost Price' (column B) box auto populates from a master template sheet, and this formula working fine now.

I now want to add a colour section 'column C' which is also a drop down list based on the selection made in column A but depending on the item depends on the colour available.

E.g Column A, drop down options 1-5 have 5 different colour options from a drop down list, Options 6+7 have 3 colour options, 8+9 have one colour option and option 10 has 2 colour options.

I have saved lists for individual items and presume it will be the VLOOKUP command but it's about there I grind to a halt.
Think thats about as well as I can explain at the moment, if required I can post an example document to display exactly how it looks.

Cheers guys
 

A:Further Excel Formula Help

Read other 15 answers
RELEVANCY SCORE 50.4

OK - I need a formula to calculate the following

In Cell A1 I will enter a number anywhere from 0-1000. In column B (I have a list from 1-1000) and in column C (I have what each numbe from column B will return with a LOOKUP formula - lets say they are people's names)

If cell A1 is equal to 6, 12, 18, 24, 30, 36, 42, 48, (you see that the pattern here is increments of 6) then I want it to ADD 1 to cell A2 if not then I want it to look up the number listed in column B and give me the corresponding name in column C.

Is there anyone who can help me - it's driving me crazy!

Thank you sooo much!
 

A:Need an Excel FORMULA - PLEASE HELP!

t long said:

OK - I need a formula to calculate the following

In Cell A1 I will enter a number anywhere from 0-1000. In column B (I have a list from 1-1000) and in column C (I have what each numbe from column B will return with a LOOKUP formula - lets say they are people's names)

If cell A1 is equal to 6, 12, 18, 24, 30, 36, 42, 48, (you see that the pattern here is increments of 6) then I want it to ADD 1 to cell A2 if not then I want it to look up the number listed in column B and give me the corresponding name in column C.

Is there anyone who can help me - it's driving me crazy!

Thank you sooo much!Click to expand...

Try,

=IF(MOD(A1,6)=0,A2+1,VLOOKUP(A1,B1:C10,2,0))
 

Read other 1 answers
RELEVANCY SCORE 50.4

I thought i had used this before but now I cant remember. If i have a column with text data in it(a1) and I want to put a formula in (b1) that puts a letter "a" in (b) if the text in (a1) contains the text "no abs test". can this be done.
 

A:excel formula help

Assume your first "no abs test" is in A3, to have the "a" put in B3 here's the formula: =IF(A3="no abs test","a").

I used the IF function. Then you can copy and paste or fill that column.

Hope that helps.
 

Read other 3 answers
RELEVANCY SCORE 50.4

Can anyone help me with the following problem,

I have an Excel 2000 spreadsheet with 7000 rows showing when a user connects or disconnects. The first column shows date in the following format “dd/mm/yyyy” and the second column shows when the user connected in the format “hh:mm” and then the third column shows when they disconnect in the same format. Finally the fourth column shows the length they were connected I need to create a time line for a complete month in intervals of every minute to show how many users are connected at any point in time over that month. . I am going to create the time line across the top of the page but I am getting stuck on the formula to compare the information and report back a valid number. If any one could help me with this It would be greatly appreciated.

Date Start time End time Length

22/04/2003 13:59 14:18 00:19
22/04/2003 14:01 14:18 00:16
28/04/2003 12:07 13:07 01:00
28/04/2003 12:56 13:45 00:49
28/04/2003 12:59 13:45 00:46
28/04/2003 12:59 13:45 00:46
 

A:Please help with my formula in Excel

Have I got this right - you want to count how many users are connected for every minute of every day of each month? That's 1440 minutes per day - 44640 in a 31 day month?

Deej, UK
 

Read other 3 answers
RELEVANCY SCORE 50.4

I'd like to create a formula (some kind of IF statement) to test a range of cells to determine if any hidden rows contain values. The statement could return a statement like "Alert - hidden values" if it returns true.

We have a billing sheet, and some of the users tend to hide rows that don't apply, forgetting to clear the values first. Then, the total does not appear to calculate correctly.

Any ideas? I know how to do the if statement, I just don't know how to test the range of cells in a formula. Thanks.
 

A:Excel Formula

Read other 10 answers
RELEVANCY SCORE 50.4

I am sure this is simply enough to do but I'm just not up to speed with excel, I am producing a simple series of spreadsheets for someone to use as a register of people attending class. So far I have 12 sheets, one per month, and on each I have the list of names and the dates they have attended. At the end of the spreadsheet I have a total for the month and also a year to date total.
What I want to know is how to I transfer the year to date total to the next spreadsheet so that by the end of the year they have the toatal for the year.
Many thanks in advance.
 

A:Formula in Excel

A1 (January), A2 (Feb), etc - A13 (total) = A1+A2+ etc
 

Read other 4 answers