hi, I just need a simple formula I have a template for an invoice here...

There is the SUBTOTAL

[email protected]%

and there is the TOTAL

The TOTAL is got by adding on a VAT @ 21%....but what formula do I put in so it will calculate the vat every time in the template?

your help would be much appreciated.

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.)

The formula would be the subtotal cell (in this example we'll use C5) times .21:

=C5*.21

Then, you would likely format the cell and select "Currency".

Hi and sorry but I have a very limited knowledge of Excel and need help quickly.I wish to count any cell in a row that has a value in it. The value is not relevant, just the fact that the cell has content.How?

The Function is Count. eg. =count(a1:z33) and press enter. Place the cursor in the cell you want the answer to be and type in the Function.

3 more repliesHelp, my sparse hair is becoming even more endangered.We are using Excel XP. Using Autosum I get a total for A1:D1 in I1 and a total for E1:H1 in J1. Simples but, after entering values in A1,B1,C1 and D1 and getting the total in I1, a value entered in E1, F1, G1 or H1 is not only totalled in J1, but is also added onto the original total in I1. If values are entered into E1:H1 before entering values in A1:D1 everything works fine. However we do not always get the second group of values for entering before the first group are available.What is even more puzzling is that for the first 100 or so rows it all worked properly but now, whether we copy the formulae down or enter a new formula for each row we get the same problem.

Don't use Autosum, it will take in all values from A1 to I1. Use =SUM(A1:D1) in I1 and =SUM(E1:H1) in J1. If I've read you correctly that is.

7 more repliesI'm trying to write a formula for a spreadsheet in Excel 2010, but I'm obviously not doing it correctly because Excel just recognizes the formula as text. What I want is a formula in cell E6 that produces a value of 30 times the value of Cell C6, but only if the value of Cell D6 ? 0.

What I entered as a formula in Cell E6 is: IF(D6?C6),E6=30*C6

I suspect it needs more or fewer parens or commas or something. Can anyone straighten me out? Many thanks.

Hi Guys,

Apologies if this is unforgivably dumb but I don't use spreadsheets much and I just can't make it work.

I'm trying to make a home finances spreadsheet, where my first column is everything that comes in, and then I have five or six categorised columns for everything that goes out, ie bills, shopping, clothes etc.

What I would like is a nice neat formula that gives me a running total. So that would be yesterday's total+anything in today-anything out today. But the problem is that I have several "out" columns and I can't make it work when only one of them has a value. I have been using:

=SUM(L3;C4)-(D4:K4)

But I'm getting a !#VALUE! error with this, and even if I type the numbers of each individual negative cell, it will only subtract the first one. I know I can do this without typing each individual cell and without putting negative values in the cells.

Please help!

Would it be possible to see a sample sheet? Obviously you don't want to share all your financial data with the world, but if you could make two or three lines of sample data that can help to explain what you're trying to accomplish.

Without that if I've understood it correctly

If L3 is the previous day's total

C4 is today's income

And D4:K4 encompasses today's expenditure

you could simply use the following in L4

=L3+C4-Sum(D4:K4)

OK, I'm annoyed with myself, cuz I've done this a few years ago for my old company, and want to recreate it for my new one, but can't get my head around it anymore. Should be quite simple for anyone with a bit of working experience with Excel.

In it's most simple form, the table lists stock movements of sample products.

The two main columns in question are:

Serial number: simple number

Movement date: date format

(New location: Text (client's name) only important in the context of information needed, but not vital to the formula)

I want to add a column which will indicate which of the movments of EACH sample was the last one. Then I can filter out those movements which are not the last one, and know where all my individual sample products are.

Logically, I understand that it has to look up all rows with the same serial number, within that group find the record with the greatest value in movement date, and output a certain value indicating it's 'true', but I can't find a formula or combination that'll do it.

Any help would be greatly appreciated

OK, I know this must be one of the simplest formulas to ask, but what formula do I use in cell J49, to find 8% of the total of H49. My mind has gone blank.

Doh! its =SUM(H49/10)

not sure that works

8%

=8/100

to find 8% of a value then its

value * (8/100)

Have been using a simple formula (for checking my home bills /account): =E1184+F1185-G1185but at row 1186 instead of a figure it comes up with #NUMBER!Why has this happened?

If it is #VALUE! do any of those cells contain text?Try re-entering the values.

3 more repliesI'm trying to use column A as a 'sequence' number (1, 2, 3, 4, 5, ...) in a spreadsheet, so I enter '1' in cell A1 and enter the simple formula =A1+1 in cell A2 (which correctly returns the number 2). But, when I copy the simple formula in cell A2 and paste it into cell A3, the value in the cell is 2 even tho the formula shows as =A2+1. This works just fine in Excel 97 and 2003, so what am I doing wrong?

Thx for any advice you can give.

I've just tested this out on Excel 2007 and it works fine on my PC. I'm at a total loss as to why it would add 1+2 and make 2?

Hello All,

Thank for taking the time.

I have a simple question.

I am organizing a football pool and I need help with a certain formula.

Players are awarded points for their statistics throughout the football season.

I have a simple formula for that. But......

Players are also awarded bonus points for how many yards they amass in a game.

Example:

QuarterBack Passing Yards per game

300-349 Yards Passing = 5 Points

350-399 Yards Passing = 10 Bonus Points

400-449 Yards Passing = 15 Bonus Points

450-499 Yards Passing = 20 Bonus Points

500 + Yards Passing = 30 Bonus Points

I have set up a spreadsheet that tracks all the players stats on a game by game status.

Is there a formula which allows me to set a value for the number of bonus points allowed depending on the value of a cell or cells?

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

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

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

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

8 more replies

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.

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.

Depends on which version of Word that you are using but Word can do simple calculations

2 more repliesI 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.

You'll have to do this:

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

Hi guys,

I'm looking at this question on a test paper for Excel. It says:

"The early booking discount is worked out as follows: if the basic cost is less than £3000, the discount is 3.5% of the basic cost. If it is £3000 or greater, then the discount is 4% of the basic cost. Write this as an IF formula.

Am I right in saying this is the correct way on writing it?

=IF(E6<3000,-3.5%,-4%)

Where E6 is the "basic cost"

thanks,

Matt

Right idea, but incomplete formula

Try something like this

=IF(E6<3000,-.035*e6,-.04*e6)

Hi,Here I am again still trying to learn and somehow lost once more. So i turn to this site which has bailed me out multiple times in hopes that i haven't gone to the well to often.Here's my problem right now. Under column B, I have numbers (1,2,3) and in column C I have letters (a,b,c). I know that by sing the =COUNT formula I can count the number of 1s, 2s, and 3s under column b as well as the number of As, Bs, and C,s under column C. What I am trying to achieve though is to count how many cells that contain "A" are under column C where Column B shows the number 1.I know this is just a formula which I can't seem to find. Hopefully someone can point me to the right direction.Thanks!

To start with, you should be very careful when posting examples of your data.First you said in column C I have letters (a,b,c) Then you said the number of As, Bs, and C,s under column C.When you mix cases, you confuse the poor reader. Are you trying to count lower case letters or uppercase letters?Assuming you meant lower case, a formula of this type should do it for you:=SUMPRODUCT(($B$1:$B$12=1)*($C$1:$C$12="a"))

2 more repliesWe have a data sheet of weights example below in Kg

0.50

0.25

0.01

0.04

0.06

6.00

1.40

1.80

If the weights are the following then value should be as follows

Under 150gms .75

150gms - 1.5Kg 1.75

1.5 Kg - 2 Kg 2.55

2 - 20 Kg 5.25

I then wrote this formulae

=IF(D6<20,5.25,IF(D6<2,2.55,IF(D6<1.5,1.75,IF(D6<0.15,0.75))))

However the answer is coming 5.25 for each cell. I do not understand. Can someone help.

Many Thanks

I have a Google spreadsheet with 2 separate columns of over 200 lines long each.

I have spend 2 days looking for a formula that will count the empty cells (the full cells have no numeric values in them, just a X) I have tried every combination possible......COUNTBLANK, COUNT, ISBLANK, ISNUMBER......

Can someone be so kind as to tell me the exact wording I need to write in the formula bar.......

I know it should be really simple but it's not to me......

:confused

Thank you

Onnha

Perhaps

https://support.google.com/drive/bin/static.py?hl=en&topic=25273&page=table.cs&tab=1240290

ISBLANK

if not remove the word INFO in the filter box and look at other commands.

Hello,I have created the macro below. I recorded it so that when I tap the button the macro is attached to it inserts a new row above my designeted cell (IMP_01). One of the cells in the new row also has a formula in it which makes it equal 2 of the other cells in the row multiplied by each other. The problem is the first time I run the macro everything works fine. The second time I run the macro the formula does not work/appear in the new row. In addition, I am using this macro and other similar ones to add new rows and 3 different sections of the same spreadsheet.Any help would be awesome, thanks!Range("IMP_01").Select Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove Range("E29").Select ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]" Range("IMP_01").SelectEnd Sub

First, a posting tip...Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum. As for your question, your code selects E29 and puts the formula in that cell. That's all it's ever going to do since you've hardcoded that range reference. It's going to put the formula in E29 every time you run the macro.In addition, you do not have to Select a range via VBA to perform an action on it. That's very inefficient. You can almost always perform the operation directly on the range within VBA.Try this. It inserts a row above the Named range and then uses the Row property of the Named range to determine where to put the formula. Basically you are letting VBA figure out what row to put the formula in instead of telling it to put the formula in a specific cell.Sub InsertFormula()

'Insert row above named range

Range("IMP_01").EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove

'Put formula in new row

Range("E" & Range("IMP_01").Row - 1).FormulaR1C1 = "=RC[-2]*RC[-1]"

End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Need help with the below excel formula to ensure the maximum value doesnt exceed 1200 - Can anyone help?=IF(AB14>59,Z14+1,ROUND(Z14+AB14/100+0.0005,2))[email protected] by moderator: email address deleted

Ok, so did my suggested formula wok for you or not?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

6 more repliesHello All,

I am having excel file aaa which is saved on my one drive . File aaa has refrence from file bbb which is saved on local drive of my laptop. Is it possible if i use aaa from another machine and if a make any changes in bbb from my computer will that reflect in aaa.

Using Excel spreadsheet, is there a way of entering a date of birth in one cell for the program to automatically calculate a person's age in another cell, perhaps using the PC date for the clculation?Just years and months, or rounded to a decimal of their age (29.5 etc)DRiM

Hi

Does anyone know a quick method or type of formula I might need to do the following:

I have a spread sheet which i record new figures on everyweek and have different catergories, with the dates monday to friday down the side

Monday to friday dates are repeated about 15 times for the 15 diff categories and the moment I have to manually key them in the 1st category and then copy and paste them into the rest

Surely there is a better way?

Thanks in advance

I'm not sure I understand your situation. For each category, you have a column with the day (Monday through Friday) and then you have another column with the dates for each day. Is that it?

So, the day cells are always simply Monday through Friday but the corresponding dates change each week. Is that it?

Suppose cells A1 through A5 are Monday through Friday, and cells B1 through B5 are Oct. 20 through Oct. 25. This would be for the first category, right? Let's say you leave a blank row between categories. So, the next category is in cells A7:A11 through B7:B11. And, so on, for all categories.

Cell B1 shows Oct. 20, which you entered. To show the same date in B7, insert the formula =B1. Enter the corresponding formula in cells B8:B11. Now, copy the formulas in B7:B11 down to each of the other categories.

Once all the formulas are setup, save this file as a template that you can use for subsequent weeks. Just change the dates in the first section (cells B1:B5) and the dates in the other categories will be updated by formula.

If I'm off the track here, can you attach an Excel file to a forum message so that I/we can take a look at it?

Ok basically, this is what I've got.

I am trying to make a formula that can find errors. I'm am not an excel guru by any means.

This thing basically needs to have an if then statement something like this:

IF the value is less than .300 from any other value THEN Null (invalid entry message)

oh yeah... The data that needs to be compared is going to be in column E lines 2-51

can you upload an example spreadsheet, would help to see the values you are comparing

are you comparing E2 through E51 and want to compare the values against each for a variance less then .3

Please can anyone help with excel formulas say I have this formula=COUNTIF(A1:F1,"M")*8 but I need the formula to only apply to one or two columns in the same row say A1 and C1 need to return the number *8 but columns B1 and D1 need to return the number *4 and then the results added together as one answer 8+4+8+4=24 Can the be written in one formula? Steve.

Hi, I am sakona, I want to know about some formula bellow,I have score like these: 10,12,20,8,A,F,19 I want to add 3point on that, after add, if it bigger than 20 please equal 20, if less than or equal 20 keep it the same score but if score equal "A" or "F" please keep it.Thaks,

I'm going to move this to the Office forum, but I doubt they want to do your homework, either.

3 more repliesI have a Excel sheet that shows budget figures, I done conditional formatting so that if it is under budget green and over budget is red, but I want to put a formula at the bottom that will count the total number of cells within the range which is named supplyfigure, then tell me out of all those how many times I was within budget.

Example of what I am looking for

Within Budget 25/48

the first number being the number of times I was within budget and the second is the total amount of cells within the range.

Thanks

Jay Morlan

Would the COUNT function work to give you the total number of cells within a range of cells that had numeric values. Would the COUNTIF function work to give you the number of cells within a range of cells that met a particular criteria like being greater than or equal to zero?

If those functions will work to get the two numbers, you could then use the "&" operator (See the help information for CONCATENATE) to build up your desired text string.

Hi all,

I remember several years ago that I used an MS Excel formula to access a single cell in a table using named ranges, but alas, I cannot remember what it is.

I think it is something like =(EF,RS) where EF is a named row and RS is a named column. Therefore the formula should access the cell containing 89. as in the table below:

.... PQ RS TU VW

AB 15 68 95 12

CD 45 78 37 28

EF 59 89 18 32

GH 38 46 49 52

The formula =sum(EF,RS) works but produces the result of 59+89+18+32(EF) plus 68+78+89+46(RS) totalling 479.

But what is the formula access only a single cell? Can anyone remind what the formula is?

Did you already look at this?

http://support.microsoft.com/kb/324861

They have some different commands there like lookup and index and a link to further information on finding data in different ways.

I'm trying to two formulas into one cell so for example I want,

=(C5/60) and =(C5/20) into one cell at lets just say D1

And by using data validation if someone chooses Male in C5 I want the formula in D1 to say =(C5/60).

And if some chooses Female in C5 I want the formula in D1 to say =(C5/20).

Is there any way to do this thanks.

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?

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"))

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

Hi All,

I have a spreadsheet attached that lists revenue and target by month for each name. I need to make a ytd formula that can calculate this. I cannot move the columns of the data around, so they have to stay in this format. Can anyone help?

Thank you!

Winnie

=SUM(B2,D2,F2,H2,J2,L2,N2,P2,R2,T2,V2,X2)

and then copy across to cell aa

then select those two cells and copy down

or am i missing something else you need

pls help,can somebody write me a simple formula (for me is complicated) in excel.what I need is this:I have D14 sheet in which I enter lenght.in H19 sheet I need formula which will do this:if lenght is up 25 then enter 200 in H19sheetif lenght is beetwen 25 and 50,or equal 25 than enter 300and if lenght is bigger than 50 or equal 50 than enter 400 in that H19 sheet.tnx :)

Try this:=IF(AND(D14>0,D14<=25),200,IF(AND(D14>25,D14<=50),300,IF(D14>50,400,"")))MIKEhttp://www.skeptic.com/

40 more repliesHey,Im building a spreadsheet of my ebay transactions. If you recieve payment from outside the UK the paypal charge is different from that inside the UK (boarder fees click here=)I have a coulmn called "Payment from inside UK?" In this column i put "yes" or "no". The next column is the paypal fees. I want to do an if formula, i think, to see if that column says "yes" then do this formula for charges, if "no" then this. Here is my failed attempt.=IF((K37=Yes,"=SUM(J37*3.4%+0.2)","=SUM(J37*3.9%+0.2)")(Column K is my Payment from inside UK?Column J is the price sold)Any ideas?Thanks for the help.

=IF(K37="Yes", J37*3.4%+0.2, J37*3.9%+0.2)

6 more repliesNeed help with a GOTO formula... I am trying to do an IF statement that will then goto a particular cell. I.e. IF a1 = checking, then GOTO e1, IF a1=savings, then GOTO e5

If anyone could help with this it would be greatly appreciated.

I am trying to find a formula to rank players in a league.At present I use Rank formula but it doesn't show a true ranking position.The ranking should be on frame diff & then on wins but at present it is only on frame diff.I can send a example to anyone with any ideas.

More repliesHello All,

I'm using Excel 2003 at work (yes I know it's an old version )

I have the following formula:

=SUMPRODUCT(('ABC''s 2011'!$D$2:$D$194>=DATEVALUE("1/7/2011"))*('ABC''s 2011'!$D$2:$D$194<=DATEVALUE("31/7/2011"))*('ABC''s 2011'!$C$2:$C$194="Ilkeston"))

Basically it looks at a sheet in a workbook and pulls out the number of times 'Ilkeston' appears during 1/7/2011-31/7/2011. Which is fine however I now also want it to exclude the data which also includes 'Not signed'

I was thinking of adding the following -COUNT('ABC''s 2011'!$E$2:$E$194<>"Not signed") But I realise that this will exclude all of the data that includes 'Not signed' and won't just restrict it to the month of July.

Is there a better way of expressing this in a formula? If you have any questions or need anything clarifying please do get in touch.

Thank you in advance

Hi, I asked a question about an Excel formula a couple of weeks back, And got several replies all very good, Except I struggle with excel So I decided in my wisdom, to go and try and find a ready made program that would do what I needed, Well basically I failed, So Here I am back cap in hand asking if anyone can help.What I require is a Excel formula that in column A Date of purchase,column BItem Purchased,Column C Purchase Price,Column D Sale Date, Column E Sale Price, Column F profit or loss on that Item, and finally Column G Running profit / Loss on all Items.If anyone Has a spare moment could they make a formula and send it to my via email I would be so grateful.This as been a project that i have been trying to sort for so long.But I just don't Understand Excel.Hoping someone can help Tony

Greetings to all,I feel a bit rude joining up and requesting help immeadiately without having anything to offer but I hope to be able to offer up input at a later date.....My problem has caused untold amounts of hair pulling and cussing at my computer screen and as you can tell by my being here pleading for help , abject failure!I will outline my requirements and have my fingers crossed somebody can offer a solution.I have a multi columned excel spreadsheet that I envisage growing substantially in the next 6 months. The Data/Sort function and Subtotals are sufficient at present but as volume increases I need to lower actual analysis time.I am trying to set up a "cockpit" that will read from the spreadsheet page when I specify certain parameters to it.Heres the eg....The list covers columns A-N and carries alpha and numerical info. I list a "job number" in column "C".I want to be able to list a specfic job number in the cockpit and excel to list all relevant information thru columns A-N onto the cockpit page beneath the input code. Take into account some jobs are multi lined hence the formula needs to understand it is reading the whole page/list to discover the necessary info.My best attempt was =IF(Sheet2!C2:C280=Cockpit!B20,+Sheet2!A2:N2,0) in the vain attempt to at least list one line.....i guess i've missed something....I throw myself at the mercy of strangers and hope somebody canA/ Decipher my above gibberingsB/ Provide a solution.Kind Regards to you all.Gary B.

To get a single line you should be able to use VLOOKUP - there is a tutorial click hereIf you want to return information from multiple rows, I think that you may need a VBA (macro) solution, unless somebody knows differently.

5 more repliesI 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.

Currently increasing a field (AJ2) based on certain criteria (If the designated cells (C5:C20 has and "X" in any of the column cells) (AJ2) would increase by (1) Now need to expand that criteria to include some additional entries as indicated at the bottom but exclude an entry of "N/C".

Current senerio;

AJ2 =SUMPRODUCT((MOD(COLUMN(C38:AZ38),2)=1)*C38:AZ38)

C38 =SUMPRODUCT((C3<>"")*(COUNTIF(C5:C20,"X")>0))

Need to expand the "X" in range C5:C20 to not be limited to just "X" but be expanded to include one of 10 specific text inputs like AWL, E/P, E/V or (7 other selected text inputs)

Thank you in advance

Winger

Winger, I cannot for the life of me work out ½ of what you're trying to do here. For instance, COLUMN(C38:AZ38) just gives you the column # for C:C, ie: 3.

This is a complete stab in the dark, but how about if you amended to something like

COUNTIF(C5:C20,{"X","Y","Z"})

?

HTH,

Andy

I use Excel 2007 and each time I open an Excel file it seems the formula bar is hidden. Is there a way to have the formula bar open by default? Also, I like 2007 versus 2003 but one thing I miss from 2003 is the help entry field in the upper right corner of the screen. Is there a way to use that in 2007 also as opposed to bringing up the help window with F1?

thanks

dennis

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.

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

Hello all. This relates to a sports league. I currently have a formula that calculates player performance based on points given for certain achievements divided by number of games played. Essentially its

((# of Goals * 2) + # of assists) / Games played

What Im trying to do is add one more parameter that weights a players score higher (or lower) depending on the number of games played (so more games played increases the score relative to another players score who has played fewer games). Hope that makes sense. Thanks in advance!

I have a column with the following data examples in it:

8

HQ2394

HL3

H

CDHLQKN3428

HQ234

CDNQ3589

HLQKN342

HQKN342

HKLQ234

HQ28

HL

H8

D98

CDQKN3429

LQKN34289

3

3

3

3

DQKN3429L

KN34

LQKN34289

LKN348AU167RWX

L

L

L

L

L

AU16RWXKNO34

AU16RWXKNO34

What kind of formula can be written so that I can identify any cell with an L, or a 7 , or a 8 in it whether they are the only character in a cell or within a set of characters?

Am struggling with an XL sheet and cannot decipher the manual at all to help me. I have a range of cells, split in rows, some of which are blank and contain no data. I wish to either seperate them from the data containing cells, or just delete them. Can anyone point me in the right direction please ?

To delete a blank row, click in it, Edit > Delete, tick Entire Row.To select several rows, click in the first one then hold the CTRL key down and click in the others.

6 more repliesI there a way or formula in Microsoft excel that i can call cells from 1 tab to another?

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?

Hi, Can you help?I want to hide any negative numbers(sums) in my spreadsheet, but when they become positive display the results. I think i need the if formula but i cant suss it out. Iknow one of you brainy lot can help ;-}

I'm no expert but here's a quick tip -In a recent version of Excel, do format cells, number, custom and from the drop down, select the format that has negative numbers in red. Simply change the "red" to "white" and the -ve numbers are hidden (sort of).You could use IF to refer to the numbers (which could be in a hidden column) and have, for example, =IF(G11<0,"",G11)in a new column to display the p+ve numbers only - which would do the trick.Hope that makes sense. No doubt some genius out there will have a better method though!

4 more repliesI have been asked to create an expense spreadsheet where Column A and B are date and text. Column C needs to be where you insert 1 through to 23 (representing an expense Code reference) with the value going into Column D. How do I create the formula that if the user inserts which ever expense code it will appear in column F and continually add the figures automatically. So for example if the user type in Line 1 code 1 and 20.00 for meals it auto goes in Column F then he also adds in Line 10 per say, another meal, that cost is auto added to the existing figure in Column F being the cumulative figure for the month.I hope that this can be done. Any help would be much appreciated. I have basic knowledge of Excel not advanced.

I hope I've understood this correctly, Lozzy. If you just want Column F to contain a running total of expenses, irrespectice of the code in column C then:Assuming that you have titles in row 1 and the data starts in row 2 then in F2 enter the formula:=SUM(D$2:D2)and press Enter. Then hover the cursor over the bottom right of cell F2 until it turns into a +. Hold down the left mouse button and drag down as far as required. This will copy the formula down, for example in F4 it should read=SUM(D$2:D4)and give a running total.

4 more repliesFOB Value <500 500-1000 1000-2000 >2000

A 5 10 15 20

B 6 11 16 21

C 7 12 17 22

D 8 13 18 23

FOB 775

CHARGES A 10

Looking at the above chart based on each customer A,B,C & D PRICES HAVE BEEN DEFINED ACCORDING TO THE FOB VALUE RANGE. NOW I WANT TO MAKE A FORMULA WHAT WOULD BE THE CHARGES ON CHANGING THE FOB VALUE AGAINST EACH CUSTOMER.

What I want to do is count columns that contain text entries, and do a running total,so if i have a 100 names or any text in the columns it will count them for me, if i add or delete an entry it will adjust the figures accordingly

Thanks

Rama

Hello I'm creating a spreadsheet and need some help on what formula to use- I only want excel to work out the vat if payment method is either Online or cheque, I believe its a sumif statement something like "=SUMif(D3=Online,Cheque) =SUM(G3*20/100)" but i keep getting error message as I don't have much experience in excel and can only remember the basics from ICT at high school

heres a link to the spreadsheet from my cloud storage https://copy.com/XZvqcSqUpOUM7eUe

any help would be much appreciated many thanks

Code:

=SUMIF(D3:D5, "Cheque",I3:I5)+SUMIF(D3:D5, "Online", I3:I5)

would work

EDIT: oh oh oh wait, i see what you mean. Hold on

=IF(CELL="Online",CELL+VAT,IF(CELL="Cheque",CELL+V AT, CELL))

So in other words...

if cell == online, calculate it with VAT. If it isn't == online, check if it's equal to cheque. If it is, calculate it with VAT, if it isn't, just display the contents of the cell, or in your case, 0

So more specifically tailored to your sheet here.... Column H would contain

Code:

=IF(D3="Online",SUM(G3*20/100),IF(D3="Cheque",SUM(G3*20/100),0))

Hi

Would be grateful for advice on what formula to use for the below.

I am trying to find out the average number of days for each job type completed (i.e. the average number of days for job types emergency, urgent & routine that have been completed). How do I write the conditional statement to do this (assuming that this can be done).

What formula do I use to get a combined average number of days to complete jobs for all completed job types (Emergency, urgent & routine).

Each job type has a target for completion, Emergency has a target date of 3 days for completion, urgent has a target date of 7 days for completion and routine has a target date of 28 days for completion. I would like to count the number of job types that were completed within target. In the example below, two of the routines were completed within target of 28 days, the data for this is the number of days to complete job is caluclated from the date received/date completed. Again, how do I do this.

I have attached the file that relates to the above.

Many thanks in advance

Hi Kets23,

I've updated your workbook with two techniques.

1 & 2. To get the averages, easiest is to use Pivot Table - needs to be refreshed whenever you change data in the source.

This shows you the averages per Job Type and the overall average.

3. I've used an array formula (needs to be entered with Ctrl + Shift + Enter) to count how many jobs hit target.

I also named the ranges so make the formulas more understandable.

Removed two blank columns to aid the Pivot Table.

Suggest you use HELP to get info on the above techniques.

lol

Hew

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

thanks

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???

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

I have a spreadsheet that has 3 columns, the third column is called category and some of the rows have multiple categories separated by a semi-colon. I would like to find the ; and then copy an additional row for each additional item in that cell and make them a new row with individual categories

for example:

name phone category

ABC 555-5555 building; foundations; building; electrical

so what I would like to end up with is:

ABC 555-5555 building

ABC 555-5555 foundations

ABC 555-5555 Roofing

ABC 555-5555 electrical

I am attaching a spreadsheet.

Please help!

Hi

The only way (I can think of) to do this is with a macro. The attached has the macro called "Split_Activities" that will create a new worksheet, deposit the results there and sort on Company.

See if this is what you want and let me know if there are any changes you need

I am creating a spreadsheet logging the times staff are present each day and i am trying to calculate a total number of hours worked formula and if on that day no hours are worked, it is showing as ##### instead of 0.I know this might be not enough information for someone to assist me directly but does anyone know of where i can look to get help.Many thanks

Are you subtracting times to get that result? Excel by default will not display negative times and puts in ##### There are two ways around this:a) Use a formula like =MOD(A1-B1,1) which will display a positive time even if B1 is greater than A1.b) In Tools > Options, switch to the 1904 date system.

10 more repliesi have a list of agents with several log ins and log outs for one day, i was trying to do a vlookup to only get the name of the agent and the first log in and the last log out i am not too familiar with this and i got stock can some body please help me with a formula for this, atached you will see the excel sheet with the info that i got from the agents log in and out.

Thanks in advance.

<Removed all the names and replaced with EMP - Number - ETAF moderator

Note: we only need dummy data - and as this is a public forum not a good idea to have real names , i assume they where , in the public domain

also solution in my last post #4 >

I'm new here so I hope this site will be helpful!

I have a sample Excel spreadsheet report attached.

In it, I have 2 tables. What I need is to copy data from Table 2 and fill in the missing blanks in Table 1. The Company Name and Email must be grouped to the name so the 'Name' in table one must have the same email and company name as in the 'Name' in Table 2.

Some of the 'Names' are the same in the tables, some names aren't there, and some names are extra. The names that aren't there must have their email and company name empty, but still need to be there at the final product. Would really appreciate some help.

I then proceeded to use the SUMIF function but that only works for numbers,

i.e

I had grouped the emails and company names to the Names in column two.

I tried to use the SUMIF function, in the case of the attached file, =SUMIF(H:H,A:A,G:G) but it did not work.

I then tried converting the words into numbers but was unsuccessful,

I would appreciate any help.

Thank you!

I'm rustier than I thought with Excel, so here goes:

I'm creating a sales report spreadsheet. Cell C2 will be the invoiced amount. Cell Q2 is the sales rep's ID. Cell R2 is the commission (C2*.2) for the rep. Cell U2 is the company net (invoice minus commission). If there is a sales rep assigned to the client, how can a value in Q2 be worked into a formula so that if there is a rep their commission will show in R2 and the company's net in U2, but if there is no rep, there will be "zero" in R2 and the full invoice amount in U2? Must there be a number value in Q2, or will any data in Q2 work?

It should be an "IF-THEN" situation, but I'm obviously missing on the fine points of creating this formula.

I hope that I've made myself clear. Thanks.

I am looking to do a time card thing on excel where workers fill out their time on their smartphones. I have attached an example of what it will look like for clarification. I need the employee to be able to put a pin number in so we can verify that each employee filled out their own hours. My question is what kind of formula can I use to check that the pin goes with the employees name and then it gives me a "true, false" or something that tells me if the pin is correct?

Hopefully this made sense.

Thanks.

Hi,

I have an excel sheet that has the words BLANK in one column and Y in another, i need to total the amount of BLANK's as well as the amount of Y's in a formula

~And i am stuck!

Any help would be gratefully received

Thanks

HI - I'm trying to get a % in excel on service levels met.

Example:

Date Expected to be complete - 10/12/2015

Date Completed - 10/13/2015

Can someone help me figure out a formula?

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.

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?

I haven't used Excel before except for basic functionality, but in the degree course I am studying we are using Excel to calculate probability. I have been given all the functions in the assignment and a photocopy of how the spreadsheet should look. However the random numbers need to extend over 500 rows and when i copy and paste row1 to row500 it will not generate the 500 results only the first.Can anybody help in what I maybe doing wrong?

tends to confuse, and the habit should be avoided :-)In the meantime does VoG's Excel Tips and Tricks click here solve your problem?Hope this helps :-)

8 more repliesHi,

I am working on a project with pulling total sum of values from one worksheet (named AR) to the other worksheet (named Joblog). I want to get the running total for the specific job number listed in the "Joblog" worksheet to reflect in the column with heading "Invoiced to date". The information will be pulled from the worksheet called "AR". I started to use this formula but it is not giving me the running total.

=SUMPRODUCT(INDEX(AR!F:F,MATCH(A2,AR!B:B,0)))

I've attached the file I have. Any help on this is truly appreciated...

"=SUMIF(AR!d:d,'JOB LOG'!C2,AR!F:F)" Without the quotes should work.

Hello, Can anyone help me with this little query? I have a list of names in a column on an Excel worksheet. I would like the cells in another column to pickup just the first initial from the original column. E.g.Column A Column BJames J Lee LDeborah DSarah SRichard RJohn J....and so on.Thank you in advance for any help.Lee

have a look at this website.Fantastic free tutorials.Hope this helps

4 more repliesWhat im trying to do is get excel to look inside a different excel file, and get the latest CPK value. to do this i need excel to check sheet 12 and see if there is a value for the cpk, if not excel should check sheet 11 and so on untill it gets to sheet 1, when it gets to sheet1, if there is no cpk excel needs to see if there is a provisional value and if not, return the answer "NO CPK"

i have put together this formula and it seems to work ok when i like 4 cells in the same sheet but when trying to use it as above it stops on the IF before sheet 4 (hilighted in red)

=IF('[01.xls]Sheet 12'!$AG$16<>"",'[01.xls]Sheet 12'!$AG$16,IF('[01.xls]Sheet 11'!$AG$16<>"",'[01.xls]Sheet 11'!$AG$16,IF('[01.xls]Sheet 10'!$AG$16<>"",'[01.xls]Sheet 10'!$AG$16,IF('[01.xls]Sheet 9'!$AG$16<>"",'[01.xls]Sheet 9'!$AG$16,IF('[01.xls]Sheet 8'!$AG$16<>"",'[01.xls]Sheet 8'!$AG$16,IF('[01.xls]Sheet 7'!$AG$16<>"",'[01.xls]Sheet 7'!$AG$16,IF('[01.xls]Sheet 6'!$AG$16<>"",'[01.xls]Sheet 6'!$AG$16,IF('[01.xls]Sheet 5'!$AG$16<>"",'[01.xls]Sheet 5'!$AG$16,IF('[01.xls]Sheet 4'!$AG$16<>"",'[01.xls]Sheet 4'!$AG$16,IF('[01.xls]Sheet 3'!$AG$16<>"",'[01.xl... Read more

Hi Guys,

Pls check out the excel sheet attached and let me know if what I want is possible.

Thanks in advance

Hi,

I am currently using the following Excel formula in cell K2 to calculate and display the average of the lowest two values in the range G2 to J2.

=SUM(SMALL(G2:J2,1),SMALL(G2:J2,2))/2

The formula works fine, but I now want it to ignore blank cells, so that if only one value is present in the range (it will be the value already in G2 from a previous season) it will display that value in K2 in the absence of data in H2,I2 and J2. However, I still want the averaging function of the current formula to happen once cells H2 to J2 start having data added.

I would be grateful for any help or hints as to how to proceed.

Welcome to the forum Macca.

Does this do what you need?...

=IF(AND(ISBLANK(H2),ISBLANK(I2),ISBLANK(J2)),G2,SUM(SMALL(G2:J2,1),SMALL(G2:J2,2))/2)

Need help in creating a formula to do the following: I need a column to show all deposits made and also a way to show a running balance whenever a entry is made in column (F) to subtract from total balance available no matter which entry is made in column (F) i.e amt paid in row 5 subtracts from column H then entry in row 21 is made that subtracts from balance then entry in row 9 subtracts etc,etc, .... would appreciate any help you can offer.

I running XP with excel 2000. .Thanks Dennis

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,

I'm creating an attendance tracker for my employer. Row A is the date of occurrence (i.e. employee was late/absent), Row B is the value of the attendance issue (1 for being absent, 0.5 for being late/leaving early), Row C is running total, Row D is the date the value is to drop off (6 months after receiving). I'm trying to create a formula so that when today's date is equal to or after the drop date in Row D, the value in row B will automatically drop off in row C. Does anyone know how to do that?

Thanks!

Please upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone

We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

An IF in column B may work

if C2 date is =or greater than date(Year(today()), Month(today())-6,day(today())

then , start sum again

So I have an issue...Hopefully someone here is literate enough in Excel to help me.

I am using this function to reference items in a table. The problem is the lookup value A9 changes, and I need the table array to change with it.

So this is the function:

Assume Cell A9 says Service, then this will work.

=VLOOKUP($A9,Service_Array,3,FALSE)

However if Cell A9 says Lease I need the function to dynamically change to say this:

=VLOOKUP($A9,Lease_Array,3,FALSE)

Somehow I need to reference A9 to change the table array option in this function.

=VLOOKUP($A9,IF($A9="Lease",INDIRECT("Lease_Array"),INDIRECT("Service_array")),3,FALSE)

Hi,

I want to be able to get excel to return a total based on adjacent cells with the returned formula being the lowest number in the other cells.

i.e. cell A1 will display the lowest number of cells A2 to A20 which will all have different numbers

Is this possible? If so can someone give me the formula

thanks in advance

Lee_1133

Is there a Formula within excel which allows you to use, the "Sheet Name" as a cell value... i.e if you change the sheet name a particular cell changes to?thanksSteve

you are renaming the sheet to something from a predetermined list of names, (which is elsewhere on the sheet), it would be possible.If Not then VBA would be needed.click hereandclick hereGood luck!Regards,Simsy

2 more repliesI must be getting rusty on my Excel formulas as I worked this out a few years back.

I have a spreadsheet to help my work out my computer system pricing.

Right now, I have COST + MARGIN = SELL PRICE

Right now, I need help on my MARGIN formula. When I sell a computer system, I want to be able to make 15% of COST PRICE, but no less than$100

15% of COST PRICE is easy

=1+(COST*15%)

However the hard part is IF 1+(COST*15%) = Less Than $100, I do want the value = $100

I am just a little lost on how i put that formula into Excel.

BTW, I do know that LESS THAN is >

At the end of the day, I want some thing that my co-worker can use without him complain then he has to manually have to work things out.

=if((cost*15%)>100,cost*15%,100)

or you could just do this

=max((cost*1.15), 100)

Sorry that should be max not min above (I can't edit for some reason, otherwise I would change it)

*Mod note*

I edited it for you bla!! I think there is a time frame on the edits.

Mak

I'm working on creating an excel formula to calculate a permit for the city. I need to have a formula that calcuates a sum of numbers, B12:B49, and multiplies it by a $1.60, which I have in G142. The problem I am having is that after $200 dollars, the muliplier changes to $1.07, in H142. There is also a minimum charge of $74.76, in I142, that needs to show up if not met. Any and all information will be very helpful. Thank you,Patrick

re: "The problem I am having is that after $200 dollars, the muliplier changes to $1.07, in H142."When does the $200 limit come into effect, after SUM(G142) exceeds $200 or after SUM(B12:B49)*1.60 exceeds $200?Basically what you need is a nested IF. Let's assume the $200 limit is based on the SUM alone.Try this and let me know how it works:=IF(SUM(B12:B49)>200, SUM(B12:B49)*H142, IF(SUM(B12:B49)*G142<I142, I142, SUM(B12:B49)*G142))Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

7 more repliesI've recently bought the 2010 excel I can't change the into currency or enter a date for : 22/08/11. I have to type in 22 august 2011. I want to total a column instead I get a formula.

You should ask this in the Office forum.You can change number, date, and currency type for a cell or column by highlighting, right clicking and selecting format cell.You can add up a column by putting a formula in a cell and adding the cell range such as:In B14 you type: =SUM(B2:B13) which will add up all numbers in cells B2 through B13 and show the sum in B14. These can be regular numbers or currency, but I doubt that dates will give you any meaningful results.You have to be a little bit crazy to keep you from going insane.

13 more repliesI know how to do the basics, sums, counts, averages....

But I have a spreadsheet that has several culumns with each row being a different date. I need to set up formula's in cells off to the side that take a sum or average from numbers in one column, but not all the numbers just the ones in certain rows. For example if I had a column of names, and a column of numbers. I would need to get the sum from the colum of numbers for a certain name only. That name appearing several times, not just once, in the column of names along with many other names in that same column.

It's more complicated then that since I would have do this with several different columns that are names, locations, and various other info that happened on each date but if you could tell me a formula command to do this that would be great.

=sumif(range, criteria, sumrange)

Also, look at the whole database section of functions. They all begin with "d", e.g. dcount, dsum, dmin, dmax, dget, etc.

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.

Unfortunately my strength with Excel is weak, and I'm not entirely sure what to search for in general...

I have a spreadsheet with a series of dates in Row 1 (with the exception of A1), data corresponding to the date in rows 2-12, and then a series of formulas below that. The dates are not completely consecutive; they correspond to "weekdays," and as such exclude weekends and holidays. I'd like to track certain changes over certain time periods, such as 1 day, 1 week, 1 month, etc.

Here's the question: if the date in the column is, say, 08/31/2010, and I want to have Excel select a cell from a column one month prior, is there a way to phrase that in a formula? (i.e., it would select a cell from the column starting with 07/31/2010, or, since that was a Saturday and isn't in the chart, select from the row starting with 07/30/2010 instead). The same question applies to 1 week prior, or 1 year prior, for that matter.

I'm suspecting that this is either significantly easier or significantly more complicated than I think it is; either way, I'd appreciate help, or at least alternative suggestions.

You can use the DATEDIF function for some of this.

To keep things easy on yourself, try splitting up the formula's logic into separable pieces. Makes troubleshooting easy, and ensures you see the correct information at each step. You can combine and refactor later once you're sure the approach you're trying is successful.

Edit: You'll likely need to write a little VBA to look for a particular cell by its value. Here's one example.

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.

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

i have a very simple formula in an excel page which is the product of 3 columns. Currently I have copied it right down the page but it leaves a '0' in each cell. is there a way that the formula will be applied to a whole column without being seen in the unused cells.

I know i can ctrl+d when i put in a new row of figures but wondered if there was another way?

thanks

I want to create a formula that would give me the following:if B5 is between 97-100 give me 25if is between 94-96 give me 20if is between 91-93 give me 15if between 87-90 give me 10

Thanks for telling us what you want to do. If you need any help with that, feel free to ask.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more repliesI have a list of sales invoices. For argument?s sake in column A I have the date paid , in column B I have the monetary value and in column C I want the monetary value if the invoice has not yet been paid.I need a formula along the lines of ?in cell C4 enter the value of B4 if there is no entry in A4 , if there is any entry in A4 do not enter anything in C4?.I hope I have explained my requirements (but maybe not as I am struggling to get this to work!). Any help will be very much appreciated. TIA.

I need a formula along the lines of ?in cell C4 enter the value of B4 if there is no entry in A4 , if there is any entry in A4 do not enter anything in C4?. In C4 enter=IF(A4="",B4,"")

3 more repliesHi,

Is it possible to have a formula count multiple criteria? for example, im trying to get the formula to count how many times it see the word H&SC but on a set day which is in another column.

I can do =countif(a1:a10, "H&SC") to count how many times it sees H&SC, but i dont know how to get it to check the day in column B1:b10

This is how my brain is thinking it.. if that makes sence? =countif(a1:a10, "H&SC)+countif(b1:b10, "17/06/2010")

Thanks in advanced,

Matt