ANmarS - Search results

Excel/VBA expert since Sep 1997 (20 years 9 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

92 Q & A posts tagged with formula

Mon
Dec 12
2011

3 questions in microsoft excel! please help me!?

If you want to change a setting that applies to Excel as a whole, such as R1C1 referencing style or showing the Formula Bar in the program window, what are your first steps?
Choose one answer. A. Click the Microsoft Office Button, and point to Prepare
B. Click the Microsoft Office Button, and click Excel Options
C. Use the commands in the Editing group on the Home tab
D. Use the commands in the Modify group on the Developer tab
..

Which of the following would allow you to insert a new column in an existing spreadsheet?
Choose one answer. A. Click in the column where you want to insert; then in the Cells group on the Home tab, click the arrow on Insert. Select Insert Sheet Columns.
B. With any cell in the worksheet selected (active), select the Insert option in the Cells group on the Home tab.
C. Click in the column where you want to insert; then in the Table group on the Insert tab, click the Table command.
D. Right click in the column where you want to insert; select Insert and then Shift Cells Right.
..


How do you adjust the column width to fit the information typed in the cells in that column?
Choose one answer. A. From Home tab, Cells group, click arrow on Format. Then select AutoFit Columns Width
B. From the Insert tab, click the Column command and choose the desired column style
C. From the Page Layout tab, click the Width menu and select the desired width
D. Right click on the column label, select Format cells and select the desired width
..


Answer

Q1 B
Q2 A
Q3 A

Monday 12/12/2011 1:15:48 AM
Sun
Dec 11
2011

How to convert to Excel formula?

Please help, im not sure about this Excel formula.
I need to enter the following equation into the formula/function bar in Microsoft Excel but not sure if its completely right .

V = 0.000045 + 1/4p0.09^2[0.15+0.035-(0.035cos180+v( 0.15^2-0.035^2sin^2*180))]

I think i got most of it right, but dont know how to do the Pi, Cos, Sin or the square root.


Answer

Pi is PI() in excel

So od this
... 1/4*PI()*0.09^2 ...

Sunday 12/11/2011 8:14:43 PM
Mon
Nov 21
2011

How do i get the total of one column based off of another column in microsoft excel 2010?

I do not want to "add" the numbers. Basically i have one column of numbers lets call it column (B) and another column (C) with one character it might be an "x" and it might be a "1" anyway. I need a formula that will tell me the total number of info in column B if there is a character in column C. Is this enough info to get help?


Answer

Yes sure
Paste this in cell in column D

=SUMIF(B:B, C:C, "X")

This one will get the total of column B, if its equivalent cell in column C is "X"

You can change "X" to be "1", or 2 , etc

Let me know

Monday 11/21/2011 5:58:10 PM
Sat
Nov 19
2011

What Kind Of Excel Function Do I Need?

Q: In cell B6, insert a function to calculate the total number of units in the apartment complex in A6. Be sure to enter the criteria range as an absolute reference. Copy the function down through B10.

In column A there is a list of different types of apartments and I need to find total units of each apartment column B. what kind of function would I use to count and recognize each number of units for each different apartment?


Answer

I am not sure what do you have exactly in A6, however, I am assuming only
paste this in B6
=COUNTIF( A:A, A6)

Then fill down

Saturday 11/19/2011 8:42:40 PM
Sat
Nov 19
2011

What function to use in excel?

I have a range of the first five digits of telephone numbers and I need to replace them into country name. so i need to compare very single digit of these telephone numbers with the range of prefixes i have before deciding the country name. is there any function to do that ?

I used lookup function but it compare the number exactly
example if i have the number 27800 and in my prefixes i only have 27 it will not recognize it.


Answer

You can also still using VLOOKUP but with some modifications

Modify your list of prefixes to have the entire number with zeros
so 27 becomes 27000, 15 to become 15000, etc

here you can use the VLOOKUP with Range lookup to be TRUE, like this

=VLOOKUP( A1, D:L, 2, TRUE)

The "TRUE" will let you search for the nearest item to the searched one before it

Assuming your list of prefixes is sorted in ascendant order by prefix number

Excel help has more details about the "TRUE" in VLOOKUP

Saturday 11/19/2011 11:42:40 AM
Sat
Nov 19
2011

Excel formula to convert gram to kilo?

Hi everybody ,

I would like to know if there is a formula to convert grams (example 375 gr) to kilos (0.375)
What is the easiest way to achieve that ?

Thank you very much for your help


Answer

Say grams is in A1, in B1, paste this
=A1/1000

That is all

That would be the easiest way

Saturday 11/19/2011 10:46:13 AM
Sat
Nov 19
2011

Sorting in excel ROW to COLUMN?

Hi,
I have some data in a column in which i want to sort as first row in first column, second row in second column third row in first column forth row in second column fifth row in first column sixth row in second column, and so on.

How can I do this?

Can anybody help?


Answer

I think I see what you are looking for
There is an Excel file called "2Cols to ColRow" that will convert your table

Check it out here
http://www.anmars.com/Work/?P=596630201

Note that this uses formulas

If this didn't help, please let me know, I can sure do if you send me more details

Saturday 11/19/2011 7:32:54 AM
Sat
Nov 19
2011

How can I convert from pipe delimited txt file to excel file?

Then convert that excel file to rss feed?

Or am I making this more difficult than it needs to be?


Answer

Excel already have Text two Columns wizard, however, you can use this one
http://www.anmars.com/Work/?P=589970000

It is in pure formulas and can be used to extract unlimited columns with certain delimiter

Once you copy and paste the content into column A fill the formulas down to extract them

Saturday 11/19/2011 3:58:51 AM
Sat
Nov 19
2011

How do I make a input Text field in Excel 2010 and how do I extract the value of it into other formula?

I'm trying to place a text field in my excel document that would be visible and editable when saving as .PDF or .CSV

So that when users enter a Value, my tables would adjust according to what they filled in.
When I go to the Developers tab, I can't seem to select the Text field option :/

Any ideas on how to do this?


Answer

Developers toolbar, doen't have textbox control, because you have millions of them already in the sheet
each cell can be a textbox control
You can modify the layout/format of that sheet to make certain cell looks like a textbox

Or, you can use Control Toolbox textbox
http://folder8.com/View/?7U5WAWLP8K72

Let me know if that helped or not

Saturday 11/19/2011 12:40:05 AM
Thu
Nov 17
2011

Please Help! How would i draw this graph on excel 2010?

http://imgur.com/IMLLE

Sorry the pic isn't rotated, and it's kinda small...
I can't figure out how to graph it:S I don't know how to insert negative x values in the graph, and i don't know how to change the default x values of 1,2,3,4, etc. to different numbers...
Please help! best answer will get 10 points today cause i'm in a ruuuush!
Thanks!


Answer

This one looks like a SIN(x) graph for me
just do 1, 2, 3, 4, etc in col A, then do
=Sin(A1)
in column B

Then graph it

hope this is not too late

Thursday 11/17/2011 4:27:33 AM
Mon
Nov 14
2011

How do I write a formula in Excel to figure out age and then if older?

Here is my question:
How would I a write a formula that figures out someone's age and then checks if that age is greater than 55? I know the birth date but am not sure on how to formulate. Nested function, if so how?


Answer

One if should do the trick
Assuming the birth date is on cell A1, paste this in B1
=IF( TODAY()-A1>55*356, "Older than 55", "Younger than 55")

let me know if this helped

Monday 11/14/2011 6:06:28 AM
Sat
Nov 12
2011

Simple financing in Excel for personal business?

Hey,


I've looked online for templates and such but didn't really come up with much.

My small business is in Repairs. So all I really need is an Excel spreadsheet (I have windows excel home and student 2010)

Could someone explain how i could make one with some basic formula.

Basically, I need a column for costs such as parts, and then the final invoice bill. And hopefully i can have a box to the side that will work out the final balance for that month/year.

Thanks,
~D


Answer

I have a very simple sheet that should do exactly what you are looking for

You can download it for free here
http://folder8.com/F/?Yan


Called "SimpleANBox" in 2007/2010 format

Saturday 11/12/2011 5:36:22 PM
Thu
Nov 10
2011

How to get an ms- excel formula to get the nos in the months col?

There are 100 apples. i need to give it to 2 ppl, every month. Person 1 is the 1st pref. he has to exhaust his quota of 80 apples, only then i can give the rest to person 2, whose quota is 20 apples. Pls help me put a formula in excel. Thanks in advancce



Total apples 100
Quota of Apples for Person 1 80
Quota of Apples for Person 2 20

Sub1 Sub 2
Apples given in Month 1 15 0
Apples given in Month 2 15 0
Apples given in Month 3 15 0
Apples given in Month 4 15 0
Apples given in Month 5 15 0
Apples given in Month 6 5 10
Apples given in Month 7 0 10


Answer

You just need to have the table set up correctly
first: put the total number of apples in certain column (Say E starting from E4)
Then in F4 paste this to get apples for person 1
=IF(E4 > 80, 80, E4)

Then in G4 to get apples for person 2
=IF(E4>80, E4-F4, 0)

Then fill those down to cover other months


let me know if that make sense or not

Thursday 11/10/2011 9:32:50 AM
Fri
May 14
2010

Can we use excel worksheet to evaluate a multiple choice question answer sheet?

for eg: to a question, 1) Prime Minister of UK is
a) David Cameron b) Nick Klegg c) Gordon Brown d) None of the above.
how to use logical formulas in EXCEL for candidates answering as a/b/c/d and to assign 1 mark for correct answer and 0 for incorrect


Answer

You need to put the answers in one row or one column
Then use the COUNTIF formula to calculate how many A's there

Something like this:
=COUNTIF(3:3,"A")

Assuming your answers a,b,c,d are wherever in row 3

VBAXLMan

Friday 5/14/2010 1:52:10 PM
Fri
May 14
2010

Excel question currency question?

Basically what i want to do is take colum m multiply it by 1.42 and show the results in colum N in the cell ajoining the original amount this make sence?


Answer

If you have the first value in cell M3
then in Cell N3, paste this

=M3*1.42

Then copy N3 down until the end

VBAXLMan

Friday 5/14/2010 11:14:43 AM
Fri
May 14
2010

How would I automatically sort the selected cell in excel 2007.?

First in aphabetical order by office location and then by asscending order. What are the steps that this can be done.


Answer

There is a file that sort any table automatically using formulas
Once you have it, you can customize the sort easily

where is it? where is it?

Ah, here it is
http://www.vbaxlman.com/Files/?ID=HlCOfq…


VBAXLMan

Friday 5/14/2010 2:02:29 AM
Fri
May 14
2010

Excel spreadsheet formulas?

If I was to make an monthly budget with excel, how would I use 4 different formulas for calculating total expenses, income, etc. I am confused on how to get different answers using different formulas>?


Answer

You need to do those 4 formulas in 4 different cells

If this is what I understood from your question

otherwise, you might need to explain more

VBAXLMan years of Excel experience

Friday 5/14/2010 1:03:04 AM
Thu
May 13
2010

What is the formula in Excel for a check box when clicked gives time and date in another cell?

I apologize if I am not explaining myself right.

I am working on a spreadsheet for tracking the work that's been done. I would like to use check boxes in a column and on another column for the current date and time when the box in a cell is selected. What is the formula and do you have an example to help me out, please?

For example the check box are in column V and I want the current time to be in the W column.

Thank you so much for helping!!!!


Answer

I can see what you are trying to do
The problem is that you can not (easily) do multiple checkboxes in a column and connect them to corresponding cells

I recommend you use the below shortcut keys:
CTRL + ;
To insert current date as constant
CTRL + Shift + ;
To insert current time in the selected cell as constant

This way, you will not have an updated version of Date or Time, means once you insert them ,they will not change.

VBAXLMan

Thursday 5/13/2010 8:59:04 PM
Tue
May 11
2010

How can you make an excel workbook, with about 1500 formulas that total from 3 other workbooks?

The auto fill does not work because it is pulling in information from 3 separate workbooks. Is there a quicker way then building all the formulas separately.


Answer

It looks like you have custom external links,
something like this

http://www.vbaxlman.com/Lessons/?ID=fjam…
This lesson will show you how to get values from several sheets

And also this one to show you how to generate external links
http://www.vbaxlman.com/Lessons/?ID=pbkw…

VBAXLMan

Tuesday 5/11/2010 8:36:52 AM
Tue
May 4
2010

How to import into MS Word ,data from two worksheets from Workbook of MS Excel file ?

I have MS-excel Data with a customer sheet with address details on one sheet & Items bought by a customer on other sheet,by using mail merge I am able to generate a letter for each customer by using customer sheet,I want to include details of items bought(from that items sheet).


Answer

I suggest you having a third worksheet that list the customers and their buying details
Then mail merge it

You might need to use formulas to do so, like VLOOKUP, MATCH, INDEX, IF, COUNTA, etc
use the customerID to grap these info

VBAXLMan

Tuesday 5/4/2010 3:40:57 AM
Mon
May 3
2010

How to make connections in excel.?

I want to make a list on Excel, it is a list of money i use. I want to write a number (Expense) and press Enter and then get another number (Remainings). For example: I have 1000 in one square, then i write 600 in another one this is money i have used, and then I want Excel to calculate for me and write 400 in the last and third square! Does anybody understand this and know how to do it?


Answer

Yes sure
You need to do the following:
1- Put the first amount (1000 in your case) in cell D2
2- In D3, paste this
=D2-C3
3- Now, put 600 in C3
From now on, put any other amount in column C starting from C3
So when you do an amount in C4, you just need to copy cell D3 to D4 and so on

Meaning anytime you add another amount, you just need to copy the corresponding cell in column D from the cell above

Does that make sense?

Let me know

VBAXLMan

Monday 5/3/2010 1:31:03 PM
Mon
May 3
2010

Need help putting quotes around my text in excel spreadsheet?

Hi,

I have an excel spreadsheet with different words in all the cells. I need to put quotes around each word. I have tried using a macro but I am new to it and so far have not been able to get it to not just paste the exact same word I used to record the macro. Every cell is a different word. I need the macro to ignore the word in the cell and just put quotes around each individual word. Can anyone help with this??? I appreciate it!


Answer

The char code for the quotes is 34
Meaning, if you use Chr(34) in VBA code
or
=CHAR(34) in Excel formulas, you will get the quote

Maybe something like this, in formula bar
=CHAR(34)&C1&CHAR(34)

or

Range("A1").value= Chr(34) & Range("A1").value & Chr(34)

Hope this will help

VBAXLMan

Monday 5/3/2010 1:00:36 PM
Mon
May 3
2010

Any microsoft excel experts here? need excel formula please help?

i know i asked this before but i never got a formula and i am still looking

i am looking for an excel formula in which i can add exactly one month to the month thats on the left of it on the previous row.

for example

Date Date
12/27/10 1/27/10

the second one i wish to show up automatically,

i know that it would be something like this:
=DATE(YEAR(C13), MONTH(C13)+1, DAY(C13))

but i am looking for a formula for the entire column so that i do not have to add the formula to each individual cell

thank you in advance


Answer

Just Copy and Paste that cell (with formula) to the cells below

VBAXLMan

Monday 5/3/2010 8:23:46 AM
Sun
May 2
2010

Can someone help me with a lookup table in Excel 2007?

I have to "create a lookup table that will help determine the letter grade for each student. The percent levels should be entered in the first column; the corresponding letter grades entered in the second column. (Assume a typical grading scale where 90%=A, 80%=B, etc.) Name the lookup table Grade_table. Arrange the % levels and corresponding grades from lowest to highest. Please help!!!


Answer

You need to use the VLOOKUP formula
In addition to Name feature

First you need to do the table, somewhere in any free area (say G1 to H10) put the percentage in G, corresponding Grade letter in H
Then, select that range, and go to Formula > Name, type in the name you have "Grade_table"

Then in cell A1, put any grade, say 76
In B1, paste this
=VLOOKUP(A1, Grade_table,2,TRUE)

You need to sort the Grade_table ascending by column G

VBAXLMan

Sunday 5/2/2010 7:40:01 PM
Sun
May 2
2010

I need to have the auto sum function in excel calculate alphabet characters?

I would like to have the sum function include alphabet when calculating sums. How can I do this?


Answer

Did you try the formula LEN
LEN is the formula to sum how many characters in a cell

So, if you add this in column D (Assuming the column C has the cells you want to sum its chars). paste this
=LEN(C1)
Then copy it down to get the length of all cells characters

Then do a regular SUM on column D

Hope this will help

VBAXLMan

Sunday 5/2/2010 6:53:36 AM
Wed
Apr 28
2010

Is there a formula for a name count within the entire workbook of excel?

I am creating a workbook in excel with January - December Spreadsheets. I need to find out if there is a formula that I can use to count how many times a name is used through out the ENTIRE workbook, not just each sheet. Thanks in advance.


Answer

You have two options:
1- is use the COUNTIF with multiple-3 dimensions ref
In English, do this
=COUNTIF( January:December!A:IV, "name")
Just make sure that the sheets January to December are in the regular order.

2- Using INDORECT to retrieve the value for each sheet (In a new sheet), then total that
- Put sheet1 name in cell A2 in new sheet
- Paste this in B2
=COUNTIF( INDIRECT( A2&"!A:IV"), "Name")
- Put other sheet names below and fill B2 down
- Total the column B to get the total plus the number for each sheet

Let me know if all these makes sense or not (from my profile)

VBAXLMan

Wednesday 4/28/2010 12:53:03 PM
Thu
Apr 15
2010

How to determine an unknown X value on excel?

So we had to do a beer's law plot in my chemistry class. I have an unknown sample's absorbance and I need to determine the concentration. We are suppose to use excel to do so. The absorbance is my Y value and the concentration is graphed on the X axis. How can I determine the concentration of my sample. Thanks for helping me!


Answer

You should have either a formula that runs the graph
Or a graph so we can get the formula
You cannot do it other way
If you have a formula, you can easily apply to get X value using it

If you have a graph, then you need to add trendline (Right click on that curve and select "Add Trendline")
Doing so will show you a window that enables you to show the formula
from that formula you can get the X value

Let me know if you did or not, I can guide you through that process if you lost

VBAXLMan

Thursday 4/15/2010 8:08:20 AM
Mon
Apr 12
2010

How do I find an 10 character alphanumeric string in a Excel spreadsheet cell with 70+ characters?

I need to find a 10 digit character string that is in a cell in Column G that has 70 plus characters. It can be located anywhere. The 10 character string will have these unique attributes:

* 10 characters long
* 1st two characters will be alpha (can upper or lower case)
* last 8 characters will be numeric

The following formula provided by this forum gives me a TRUE return if the this string is the only data in the cell, starting in position 1.

=AND(LEN(F2)=10, CODE(LEFT(F2,1))>64, CODE(MID(F2,2,1))>64, ISNUMBER(VALUE(RIGHT(F2,8))))

How can this formula be modified to:

1.) Allow me to find the string anywhere in the text
2.) Place the 10 digit string into Column H

Thank you.
Your assistance is appreciated.

Mark


Answer

I would do it if I only have the file

mail it to me so that I can do what you want

Trust me, If Excel can do it, then VBAXLMan can do it

Monday 4/12/2010 5:10:35 AM
Sun
Apr 11
2010

How to do in excel when i key-in data in A1 the information in B1 and B2 will appear at C1 and C2?

for microsoft excel 2003


Answer

Paste this in C1
=B1
And this in C2
=B2

That is all

VBAXLMan

Sunday 4/11/2010 6:46:54 PM
Sat
Apr 10
2010

How do I make a comparison chart using formulas in Excel?

I have an assignment to do where I need to research three different computers and make a comparison chart to find out which is the best computer for the money. The only problem I'm having is that I was never taught how to do this using formulas and/or functions. What formulas would I use to compare different things and find which is the best?


Answer

Your question is so general
But let me try
You need first to build the table
Based on the data you have, you need to study formulas like
=A1/A2
or
=
besides IF, SUM, AVERAGE
most of these formulas are in Insert > Functions (if you have Excel2003 or earlier)
or in Formulas tab > Insert Functions (If Excel2007)

Let me know if that worked or not

VBAXLMan

Saturday 4/10/2010 2:36:50 PM
Sat
Apr 10
2010

When using Excel how is a percentage added for the cost of living at 4%?


Answer

Paste this in cell B3 assuming your cost of living is in cell C3
=C3+C3*4%

or, you can also put the 4% in another cell, say H1
In this case formula in B3 would be
=C3+C3*$H$1

VBAXLMan

Saturday 4/10/2010 10:33:10 AM
Fri
Apr 9
2010

How do I populate corresponding cells with a code in excel?

Hello Everyone,
I have been struggling this for so long , your help will be much appreciated.
I am trying to make an invoice for a sales order with many different items. I need to know how I can populate the corresponding cells automatically from data stored somewhere else when I enter the product code for the specific item so I don't have to always enter it manually. For example, if I enter product code A-1 on cell A, I want the other cells on the line to be filled out automatically like "Hair Styling Iron" on cell B and "$50" on cell C and so on...

Thanks!


Answer

Once you have this table in some sheet (say Sheet1)
A, B, C, D
ID, Name, Description, Price
ID1, Name1, Hair Styling Iron, 50

Then in your invoice sheet, let me assume you have cell B15 has the Item ID that you want to populate its values, then in cell C15, paste this
=VLOOKUP( $B15, Sheet1!$A:$D, 2, FALSE)
And paste it down to fill other items

Then in D15, paste this
=VLOOKUP( $B15, Sheet1!$A:$D, 3, FALSE)
And paste it down to fill other items

=VLOOKUP( $B15, Sheet1!$A:$D, 4, FALSE)
And paste it down to fill other items

And you got what you are looking for

Let me know if that make sense or not

VBAXLMan

Friday 4/9/2010 9:02:55 AM
Thu
Apr 8
2010

How can I add a column of cells in Excel like C3 +C49 Excel says the formula is wro?

Addition of a Column of Cells

=(C3:C49)
Excel says the formula is wrong why?

= SUM (B1:B3)


123


Answer

I don't understand the problem

Try this though

=SUM( C3:C49)

VBAXLMan

Thursday 4/8/2010 4:50:31 PM
Thu
Apr 8
2010

How can I link two separate workbooks in MS Excel 2000?

I am looking to link some formulas so that whatever I enter in January workbook will then go to February automatically then march and so on. I have tried copying and pasting special but that just copies the formulas what I really want is for them to talk to each other. Being not as computer savvy as most the answer in the most basic terms would be highly appreciated.


Answer

When you do Paste Special
Select "Paste Link"
This will do it

OR

You can go to February sheet, select a cell and type
=
then select the cell you want to link to in January sheet



VBAXLMan

Thursday 4/8/2010 12:20:38 PM
Tue
Apr 6
2010

Excel formula help. In a list of numbers, is there a formula that will tell me the top 3 numbers?

I have a variety of numbers in a vertical list. Is there a formula to input that will tell me the greatest 3 numbers in the list? And another to tell me the least 3 numbers?


Answer

Just like siti Vi said
use this to get the largest three numbers in cells B2, B3 and B4
=LARGE( A:A, 1)
=LARGE( A:A, 2)
=LARGE( A:A, 3)

But adding the second request

Paste this formula in C2 to get smallest one
=SMALL( A:A, 1)
And this in C3 to get 2nd smallest number
=SMALL( A:A,2)
and this to get 3rd smallest
=SMALL( A:A, 3)

VBAXLMan

Tuesday 4/6/2010 9:09:48 AM
Mon
Apr 5
2010

How do you run a macro within a formula in microsoft excel 2007?

.... for example If(A2=A3, "Run Macro", "")

thats what i want to do, i just dont know how to do it


Answer

A formula is ACTUALLY a macro
It is FUNCTION macro and not SUB macro

So, to do that, you need to replace the lines of your macro
from
Sub WhatEverName()
....
End Sub

TO

Function WhatEverName()
...
End Function

AND, putting that function in a module (and not a form module) will enables you to run it as formula
However, you need to go to "User-Defined Functions" in the Insert Function dialog and you should see it there

Keep in mind that this is a macro, means that you need to have the macros enabled for that file to make it run

Also, you can run that formula from any opened file, as long as the file that has the function (the function macro) is open

You can see it in action in this free file
http://www.file1.net/project.asp?id=b2vi…


Does that make sense? let me know if not

VBAXLMan http://www.file1.net/project.asp?id=b2vi…

Monday 4/5/2010 4:33:00 PM
Mon
Apr 5
2010

How do I make an abridged frequency table using excel?

I have a large data set, they're ordered but I want to create an abridged table i.e (20-29, 30-31, etc..) Halp. x


Answer

You need to do it in formula
If you have Excel 2007, then the function SUMIFS will help you

http://office.microsoft.com/en-us/excel/…

VBAXLMan http://office.microsoft.com/en-us/excel/…

Monday 4/5/2010 9:10:47 AM
Sun
Apr 4
2010

Create an automatic table based on the past three months?

hi, i have an excel sheet with the past five years data on it. Each row represents a month so each month it gets one row longer.

I already have a defined range that selects the most recent 3 months, but i need to know how to display the last 3 rows and the field headings on another sheet in a specific position by the click of a button(macro).

I can make a line graph using the last three rows of data with just a click of a button and now i want to show the data used underneath the chart.

Thanks for all answers


Answer

You don't need a macro to do that
You can make it in Formulas which means faster, auto updated, etc

Let's start with some assumptions:
- You have two sheets (Sheet1 has the full data and Sheet2 to have the last three months)
- In Sheet1, your data starts from cell A1 along to V1, with headers in first row and the data below it.
- In Sheet2, you want the last three months to be in A2 to V2, A1 to V1 has the row headers

Now, in Cells A1:V1, just copy the headers (you need to do that once)
In cell A2, paste this
=OFFSET( Sheet1!$A$1,COUNTA( Sheet1!$A:$A)-ABS(ROW()-1),COLUMN( )-1)

Then copy A2 along to V2, then copy Row2 into Row3 and Row4

These three rows will bring you the last three rows from your table in Sheet1 assuming you don't have any empty cells in that table in column A
And when you add new values at the end of that table in Sheet1, these will automatically updated

Does that make sense?

let me know

VBAXLMan

Sunday 4/4/2010 11:13:43 AM
Fri
Apr 2
2010

How do i set up a formula in excel using "choose"?

i've got this for example : "11.Jul.95" and need to use a formula ( which i guess is "choose") to pick the season like July - Summer
please helpp!!! :D


Answer

Actually it is like this
If your date is in cell B1, then in C1 paste this

=TEXT( B1,"mmmm")&" - "&CHOOSE( MONTH( B1),"Winter","Winter", "Spring","Spring","Spring", "Summer", "Summer","Summer","Fall","Fall","Fall", "Winter")

This will show it exactly how you want it

VBAXLMan

Friday 4/2/2010 12:56:16 PM
Tue
Jun 9
2009

In excel, is there a formula to select the last cell of worksheet to calculate values in the next worksheet?

I currently used this formula:
='NSN Motorsports'!A12+1
In this example my previous worksheet is "NSN Motorsports" A12 is the last cell in that worksheet. I used this formula to create the next number on the new worksheet. This requires me to go back to the previous worksheet and click the last cell. My workbook will have many worksheets, so doing it this way is very time consuming. Any ideas for formulas that will not require me to leave my current worksheet to get a value from the previous worksheet?


Answer

You can create a pattern for your sheet names
After that, use some formulas to determine "the previous" sheet to the one you are in...
1- In an empty cell (make it B1), paste this
=MID(CELL( "filename",A1),SEARCH( "]",CELL( "filename",A1))+1,500)
This will give you the name of that sheet, so copying that cell into another sheet will give the name of that sheet, etc
2- Now, If your 'Pattern" is NSN1, NSN2, NSN3, then paste this in B2
="NSN"&VALUE(Mid(B1,4,1))-1
This will give you the name of the "Previous" sheet to the one that you are in
So if you are in NSN4, B2 will give you NSN3, and so on
3- Finally, to get the last value of the "Previous" sheet, do this
=MAX( INDIRECT( B2&"!A:A"))+1

Good luck with that, this will be totally automated method, justcopy and paste the three cells into the new sheet

If you need more info or think that you can customize it more, please contact me here

After all, it is the VBAXLMan here

Tuesday 6/9/2009 11:08:54 AM
Mon
Apr 27
2009

Microsoft Excel?????????

I need help finding an if/then statement.
I am looking to find the if/then statement for the bonus-if the employee has worked 40 or more hours they get a 5% bonus based on their Gross pay. If they work lee than 40 hours they get a 1% bonus based on Gross Pay. My hours worked column is in E10 and the gross pay column is in F10. nd the bonus column where I am trying to find the formula is in G10.


Answer

Paste this in F10
=IF( E10>=40, F10*5%, F10*1%)



VBAXLMan

Monday 4/27/2009 5:37:26 AM
Sun
Apr 26
2009

How to calculate expected return and correlation coefficients in excel?

Hi everybody. Someone have any idea how to calculate and what excel formula i have to use to find the expected return and correlation coefficients if I have the following data:

Date Open High Low Close Avg Vol Adj Close*
1 868 872 827 866 7083169900 866
2 855 876 836 870 6839301900 870
3 840 857 815 857 6226187600 857
4 809 846 780 843 6286869900 843
5 772 833 772 816 6952819900 816


Answer

The CORREL function will give you the correlation coefficients, like this
=CORREL( A1:A5,B1:B5)
To find it between the two arrays A1:A5 and B1:B5

VBAXLMan

Sunday 4/26/2009 5:38:15 AM
Sun
Apr 26
2009

How to made a 'formulae spreadsheet'.?

Does anyone know how to print a excel (2007) document the displays the formulas used to create the numbers.


Answer

First you need to show the formulas
- Click the OFfice Logo at the top left corner
- Excel Options > Advanced
- In the "Display Options for this worksheet" select "Show formulas in cells instead of thier calculated results"
- Ok
Now you can print it with CTRL+P as usual

Good lock

VBAXLMan

Sunday 4/26/2009 4:03:30 AM
Sat
Apr 25
2009

I want to add no. of hours spreaded in multiple rows in excel, can any body tell me the formula for the same?


Answer

If you have these cells values entered as numbers (and not dates), then use the regular sum function, like this
=SUM(A1:A5)

If you have these cells values as times (or dates) then that would requires some work
Try using this one
=HOUR(SUM( B:B)-INT(SUM( B:B)))

Where you have the dates in column B


VBAXLMan

Saturday 4/25/2009 8:50:20 AM
Sat
Apr 25
2009

I want Convert Numerical numbers convert in characters in excel.?

What’s the formula for the converting while I am use a formula =num2text(e5) e5 is a cell where is a numerical digit.
Example 100 hundred
101 hundred one
102 hundred two
Tell me what’s problem in above formula or if any other suggestion.


Answer

Here is the file that does that

http://www.file1.net/project.asp?id=KoFR…

It can do English and Arabic


Enjoy it

VBAXLMan

Saturday 4/25/2009 4:34:20 AM
Thu
Apr 23
2009

I need an MS Excel Formula that generates the total time taken from a list of times.?

Example.

I have a list of times (clock times) and I need to get the total time (duration).

5:30 am
5:45 am
6:15 am
6:30 am

The total duration time spent should be 1 hour and 15 mins or 75min.

Can I get an excel formula that does the above?


Answer

I am not sure if I got you correct here, but let me try this...

Option 1:
Now, if you have that list starting from cell A1 down
Paste this in B1
=MINUTE(A1)+HOUR( A1)*60&"min"
Then paste this down to get the others

That is one possibility answer to your question

Option 2:
Another one is this
=HOUR( A1)&" hours and "&minute(A1)&" min"

Option 3:
Or this
=SUM(A:A)


Let me know

VBAXLMan

Thursday 4/23/2009 11:41:34 PM
Thu
Apr 23
2009

Excel > Use formulas on the last x rows of data?

I'm keeping track of the total number of wins and losses for a sports team. There's one game a week. I will have 16 rows in my Excel sheet to mark a win or loss. If the team wins that week, I'll put in "W" for that week's row, and "L" for a loss. As you can imagine, a new row will be populated with either a "W" or "L" as the weeks progress.

I'm trying to get a win/loss count of the last 5 games played. So if the team has played 7 games, I want to show the total number of wins and losses of the last 5 games (games 3-7). If 10 games have been played, then show the total number of wins and losses for games 6-10. This is similar to the L10 statistic for baseball, hockey, and other sports, but in this case, I'm trying to do a L5 for the last five games.

How can I achieve this in Excel? I'm going to use COUNTIF for counting the total number of "W" and "L", but I need it to count only the last five rows of data.

Any help is appreciated. THANKS!


Answer

Do this so that whenever you add new row/week it will automatically updated

Now, if your list of Ws/Ls starts from A2 down, paste this in D1
=COUNTIF( OFFSET(A2, COUNTA(A:A)-6,0,5,1),"W")
To get the number of Ws in the last 5 non empty cells in Column A

And do this in E1
=COUNTIF( OFFSET(A2, COUNTA(A:A)-6,0,5,1),"L")
To get the number of Ls in the last 5 non empty cells in column A

Also assuming you have the cell A1 with text as header

Good luck


VBAXLMan

Thursday 4/23/2009 4:19:56 PM
Wed
Apr 22
2009

How do I write an excel formula to display an asnswer if a Number in one cell appears in another column?

I need to find out a formula that will display an answer in a blank Cell if the number contained in another cell in the same row is present within a whole column on another worksheet.

For example, if 1 is present in cell B1 in worksheet 1, then I want the formula to be able to search the whole of column C in worksheet 2 for that number (1) and if it is present display a custom text (e.g. training) in the blank cell A1 on worksheet 1.

I know my description is pretty garbled, but if someone could help I’d be eternally grateful.


Answer

I guess you want to know the famous VLOOKUP function

Here is a free video lesson that talks about that
http://www.file1.net/lesson.asp?id=b1vi8…


Check out other lessons below

VBAXLMan

Wednesday 4/22/2009 2:26:46 AM
Sat
Apr 18
2009

Can you format an Excel cell with a formula and without using a macro or the Conditional Format menu?

Can you enter a formula directly into a cell to change the format of the cell, mainly the font color or the background shade color?


Answer

No
No formula there to change the format of another cell (If not macros nor Validation)

Forget it

Sorry!

VBAXLMan

Saturday 4/18/2009 12:52:04 AM
Sat
Apr 18
2009

VLOOKUP & HLOOKUP IN MS-EXCEL?

plz. tell me in brief abour "VLOOKUP & HLOOKUP" with examples.


Answer

Here is a free video lesson about VLOOKUP
http://file1.net/lesson.asp?id=b1vi8438X…

If this is basic for you try these
http://file1.net/search.asp?query=vlooku…

VBAXLMan

Saturday 4/18/2009 12:04:40 AM
Thu
Apr 16
2009

How to deduct tax from wage in excel?

I have a spreadsheet set up in excel to manage my finances (in truth, it is in Open Office, but it is so similar it may as well be the same).

I have it set up so I enter my hours, and it calculates, depending on my hourly rate) my wage for the week / month / year; as well as a few other things.

This works well, but gives me a pre-tax figure.

I was wondering if anyone could think of a formula to use to deduct tax from the figure? Would be very helpful.

If it helps, I am in the UK, and am on Tax Code 647L.


Answer

Put the Tax percintage in any cell (say cell A1)
Then use this formula to deduct the tax

=B1-B1*A1

Where B1 has the amount before tax

You can also do this to add tax
=B1+B1*A1

VBAXLMan

Thursday 4/16/2009 1:40:09 AM
Thu
Apr 9
2009

Microsoft Excel question?

I have a column of percentages E127:E132 and I need a formula that will return one answer ($250.00) if there are three or more items in the column above 98% and another answer ($0) if there aren't.


Answer

=IF( COUNTIF(E127:E132,0.98)>=3,250,0)

Paste this in a cell

VBAXLMan

Thursday 4/9/2009 8:23:55 AM
Thu
Apr 9
2009

How do I create a formula that pulls information from one excel spreadsheet to another?

I have multiple spreadsheets in the same excel file and I am trying to create a formula or a set of formulas that will recognize a number, compute how many of the items have that number and then on a separate spreadsheet, show that sum. What I am working with are 7 spreadsheets- 4 weeks, month, quarter and year. In the week spreadsheets, an incident is labeled with a 1,2,or 3 depending on its severity. I need a formula that would calculate all of the 1s, 2s and 3s separately as their own value and then place that value in my month spreadsheet in the 1, 2 or 3 category.

Thank you in advance for your help.


Answer

If you have:
1- The week sheet name is "week"
2- The range where the 1s, 2s and 3s are in "week" sheet is A1 to A20
3- The month sheet name is "Month"
4- 1 in cell A1 in "Month" sheet
5- 2 in cell A2 in "Month" sheet
6- 3 in A3 in "Month" sheet
Then paste this in B1
=COUNTIF( Week!$A$1:$A$20, A1)
Then copy it to B2 and B3

VBAXLMan

Thursday 4/9/2009 5:20:30 AM
Thu
Apr 9
2009

I want to know the Ms-excel formulas. to use excel.?

I want to know different formulas in excel like vlookup,hlookup,pivot table,conditioning formats & links ect.,


Answer

There are a lot of websites that can help you
try these

http://MrExcel.com
http://www.File1.net

VBAXLMan

Thursday 4/9/2009 12:16:34 AM
Wed
Apr 8
2009

I received one excel Sheet from my friend,in that sheet?

he mentioned DATE in one cell,when i select that cell the TIME also appear in FORMULA BAR.but i cannot see TIME in the cell.Pls explain how to make the setting.

And also how to type in cell superscript or Power with the numbers.

thanks & regrads
lahhak


Answer

The cell content is not all the time what shown on the cell output
That is called format
Meanning that the cell may not show everything in it
You will need to change the format of the cell

To do that, you need to select that cell, press CTRL+1 and choose another format from the list
I would recommend to go to Custom and do this
yyyy-mm-dd hh:mm

To do Superscript or Subscript, you need to do almost the same

- Select the text you want to superscript (not the cell only, but the text inside that cell)
- CTRL+1
- Font
- Superscript or subscript

Enjoy it

VBAXLMan

Wednesday 4/8/2009 8:57:31 AM
Wed
Apr 8
2009

Please help with an excel formula?

I am trying to make a cell look at a specific letter combination, please see my unsuccessful formula below:

=IF(I7=HO,12,IF(I7=RO,12,IF(I7=ID=12,I…

I would like it to look for them specific letters & return the number that corresponds with it.


Answer

I would rather you to do the proper way
Which is:
1- Put the possible values expected in cell I7 in range A1 to A5 for example
2- Put the corresponding value to the cell next to it in B1 to B5
3- Now paste this in cell J7 (Next to I7)
=VLOOKUP(I7, $A$1:$B$5,2,FALSE)

Enjoy it

VBAXLMan

Wednesday 4/8/2009 7:50:56 AM
Tue
Apr 7
2009

Percentages in excel?

how do i calculate percentages on one column and post them on the next column?


Answer

If you have the number you want to get its percentage in A1, paste this in B1
=A1*50%
To get 50% of the value in cell A1

Then copy and paste B1 down to match all values in column A

VBAXLMan

Tuesday 4/7/2009 8:15:32 PM
Tue
Apr 7
2009

In Excel How do i copy values from every fifth row(1st,5th,10th.) and paste it in every second(1st,3rd,5th)?


Answer

Here is a video lesson on how to do that
http://www.file1.net/lesson.asp?id=b2wia…

It is called How to delete rows, you may need to modify the formula a little bit to achieve that

Enjoy it

VBAXLMan

Tuesday 4/7/2009 2:35:50 PM
Tue
Apr 7
2009

Filtering data in Excel 2007 based on formatting?

I have two columns I need to filter. Some have text and some have text with strike-through (a line going through the text). Does anyone know how I can filter based on the strike-through - or if that's not an option can I filter based on anything that is not formatted like the regular text so it will separate the cells with the strike-through? Thank you.


Answer

There have been a lot of requests to read format through formulas

Since it is nit supported yet, here is a user definec function to do that

Function IsStrickeThrough(CellAddress As Range) As Boolean
If CellAddress.Rows.Count = 1 And CellAddress.Columns.Count = 1 Then
IsStrickeThrough = CellAddress.Font.Strikethrough
End If
End Function

To use it...
- Press ALT + F11
- Insert > Module
- Paste this
- Now go to any cell and use it like this
=IsStrickeThrough( A1)
Assuming the cell you want to check is A1



Enjoy it



VBAXLMan

Tuesday 4/7/2009 9:01:21 AM
Mon
Apr 6
2009

I am subtracting 2 cells i a excel sheet. Keep getting wrong answer in the same cell off by .01 cent format ok?

Example answer should be $10.20, answer I keep getting is $10.19. All my formatting is o k, all formulas are o k, can only get right answer when I format to 1 Decimal place instead of 2


Answer

The number that Excel shows in a cell is not all the time the same number that is calculated
for example
If you have 10.48 in a cell, and formatted that cell with 1 decimal place, then it shows as 10.5
However the number is taken into calculation is not 10.5, but it is 10.48

That may be what you are facing in your example

You can do that:
1- Go to Tools > Options (In Excel2003)
Logo > Excel options (in Excel2007) and search for the setting that says "Precision as displayed"
This will make Excel treat every number (in the current worksheet) as it is displayed, and by then you will see correct values

VBAXLMan

Monday 4/6/2009 2:33:01 PM
Mon
Apr 6
2009

Not Show Excel Formula Results?

hi

I have a formula with IF function in a cell and I dragged it to replicate the formula .

This added FALSE in all the cells how do i avoid that and have nothign displayed but the formula will still be active in that cell

thanks


Answer

Use this mask

=IF( [condition] , "", [result])

Something like this
=IF(A1-"", "", 40/A1)
This will show nothing if the cell A1 is empty

You can also try this
=IF( NOT(ISNUMBER( A1)),"",40/A1)


Enjoy it

VBAXLMan

Monday 4/6/2009 1:40:53 PM
Mon
Apr 6
2009

Excel spreadsheet question. =IF( OR(A1="", B1=""), "", A1+B1) How do I do this for a large range of cells?


Answer

If you have that formula in cell C1
Just copy C1 to C2 and see what the formula is in C2

You will that every 1 in A1 and B1 had become 2

Try it, that is called Relative references

So... Copy sell C1, select the target range (may be C2:C500) and paste


VBAXLMan

Monday 4/6/2009 1:36:32 AM
Sat
Apr 4
2009

Microsoft Office Excel 2007 counting months?

example
how do i count the amount of months i have been employed
i want the worksheet to update automatical everytime i open it, no static values

Start Date =DATE(2007,8,22)
Today Date =TODAY()
Months
Days =B8-B7 = 592

whats the formula to count the months..is there a easy way
any help much appreciated...thanks


Answer

Based on how you want to show the number of months (Integers or parts) you got several options:
1- do this in any cell
=(B8-B7)/30
This will give you how many 30 days this period has

2- You also do this
=INT(( B8-B7).30)
To get how many full months

VBAXLMan

Saturday 4/4/2009 1:34:45 PM
Sat
Apr 4
2009

How to solve this problem with Excel conditional formatting?

I have a table on an Excel spreadsheet, which table contains only numbers. I want to color a "path" in this table using the following algorithm:

1) Make current the upper left cell and color it.

2) Examine the cell to the right of the current one and the cell beneath the current one. Make current the one that contains the smaller of the two numbers and color it. If the two numbers are equal, make current and color the cell beneath the current one.

3) Repeat 2) until the bottom or the right edge of the table is reached, whichever occurs first.

Can this be done using only the means of Excel's conditional formatting (i.e., no VBA) and how? That is, what formulas for conditional formatting to use?


Answer

Conditional formatting will "FORMAT" the cell and not change the cell content

To change cell content (or value) you will need a function

Based on your request, IF function would do the trick for you
Now, if your first cell is A1, then paste this into it
=IF( OR(A2="",B1=""),"", A2<B1, A1, IF( A2>B1, B1, A2))

Then fill cell A1 down

Now, Since the Conditional Formatting is to "FORMAT" cells, I don't see where you need it here

let me know

VBAXLMan

Saturday 4/4/2009 11:14:45 AM
Fri
Apr 3
2009

Working with Excel 2007?

In Excel 07 how do you put in the formula to make time add (i.e.)
1330hrs - 1500hrs = 2hrs. 30mins in one cell


Answer

What you need actually is to convert a regular number to a hr. min format
So, you can do this
=INT(A1/60)&" hrrs. "&A1-INT(A1/60)&" mins"

This will convert the number of hours in cell A1 into the format you wanted

Since all you have are numbers of hours doing the add is just like any other numbers

Or, you can do this
=INT(( A1-B1)/60)&" hrrs. "&(A1-B1)-INT(( A1-B1)/60)&" mins"

To subtract B1 from A1

VBAXLMan

Friday 4/3/2009 9:41:41 PM
Thu
Feb 12
2009

How to create a function in Excel...a second question?

Thank you to the person that helped me yesterday...a huge help! The isanswer and match functions worked great!

I now need to write a formula to do the following: I need to find if the value in cell C1/Sheet 1 is in column D/Sheet 2. If true, return the value (text) in Column E/Sheet 2 that is in the same row as the value found in Column D. If false, return "null" or "false".

I tried the same isanswer and match formula as it seemed it was close to the solution but it didn't work. Can you help me again? :)


Answer

Paste this in cell E1 Sheet2
=IF( ISNA(MATCH( Sheet1!C1, Sheet2!D:D,0)), "","Text")

And fill it down

----- Edit ----
Yes, in this case, use this
=IF( ISNA(VLOOKUP( Sheet1C1, Sheet2!D:E,2,FALSE )),"Not found",VLOOKUP( Sheet1C1, Sheet2!D:E,2,FALSE))

This whould work

VBAXLMan

Thursday 2/12/2009 3:18:14 PM
Wed
Jan 21
2009

Excel Formula - I need help!?

Hi,

I'm not sure if I'm supposed to use the If formula for this - and if so, how. Here's what I want to do:

Something has X views, 10,000 for this example. I want:

1) The first 100 views to be worth 3 points each (=300 points)
2) The second 100 views to be worth 2 points each (=200 points)
3) The third 100 views to be worth 1 point each (=100 points)
4) Any view thereafter to be worth 0.75 of a point (in this case, 9,700 views times 0.75 = 7,275) points.

How would I create a formula like that (without having to actually divide up the views into four groups and do the separate calculations?)

Thanks!


Answer

So these views are in column B I assume starting from B1
Then in C1, paste this
=CHOOSE( INT( ROW()/100)+1,3,2,1,0.75)

And copy it down to the end of your list

This column will give you the points you have to multiply by, just add another column multiplying the value you want time this, or change the C1 into
=CHOOSE( INT( ROW()/100)+1,3,2,1,0.75)*B1

VBAXLMan is back

Wednesday 1/21/2009 2:42:33 PM
Tue
Jan 20
2009

Get answers from millions of real people.?

In excel im having data in 3 columns like Column ABC in 1000 rows each column
in column A data is greater than or equal to B in B colum data is greater than or equal to C. how to get the result what is the formula


Answer

In column D paste this
=IF( AND( A1>B1, B1>C1), "Ok", "Not Ok")
and fill it down

That is what I understood from your question, you didn't gave enough info

VBAXLMan is here again to feed your Excel needs

Tuesday 1/20/2009 11:57:27 PM
Tue
Jan 20
2009

What formula do i use in excel if i want a certain column to say 0 if another column equals 0?


Answer

Paste this in B1
=IF( A1=0,0, "Not zero")
Copy and paste this down to any range of cells

this will give column B 0 if column A is zero

VBAXLMan

Tuesday 1/20/2009 8:45:41 PM
Tue
Jan 20
2009

Why does a formula remain visible in a cell on an excel worksheet?

I have many formulas in my excel spreadsheet and when I want to extend the search a few more rows, and change the number in the formula bar, the formula appears and then remains visible in the cell. Please email me if you have a solution on diddlesjnr@adam.com.au


Answer

Perhaps you have the format for that cell as text
Excel does that if you:
1- Have no equal "=" sign as the FIRST character
2- Format that cell as text

VBAXLMan is back

Tuesday 1/20/2009 8:09:30 PM
Mon
Jan 19
2009

Excel % formula non-blanks?

I have an xls where I want to enter a formula that counts the # of non-blank cells & divides by # to get a % result. i.e 56 non-blank cells/735=8%. Can anyone show me how to do this?


Answer

Sure, COUNTA will do it for you
like this

=COUNTA( A:A)/735

Format that cell as Percentage to get the %

VBAXLMan is back

Monday 1/19/2009 2:57:55 PM
Mon
Jan 19
2009

Display date and time together in a cell when using a DatePicker?

Hi, I added a date picker add-in from the following url. into excel
http://officeblogs.net/excel/samradDatePicker.xla

Ensured that i make a column accept dates. and now when i click on that cell i do get an icon to enter the date.
My question is the output comes as only date "01-mar-09" if i want the time also to be there along with date(in the same cell), what do i have to do.
I tried changing format in DATE in cell formating but its not working.
anyway i can get this done
Thanks


Answer

It is a "DatePicker" and not a timepicker
That was 1.
2. what time do you want to use if the user didn't select one?
3. You can do the following:
Do a validation in the next cell with the expected time (as hours or halves) then allow the user to select from it

The date usually uses the integer part on the number to refer to the date, time uses the decimal part

So if your date is Jan-19-2009 (means=39832) then the time of that would be 12:00 AM

So when you select a date in DatePicker, you are actually selecting the hour 12:00AM in the morning of that date

To add a time, you will need to do a validation or do some other macro code

VBAXLMan is back to feed your Excel needs

Monday 1/19/2009 11:37:50 AM
Mon
Jan 19
2009

Nesting IF and SUMIF?

I'm trying to create a summary document of invoicing for my company and was wondering if Excel has any functionality that allows me to look through a table of vendor invoices that have been paid, identify all invoices that have been coded to a particular project, identify all instances of one particular vendor within the list of invoices for a specific project, and then sum all invoice values that are associated with the instances I identified of the particular vendor.

So, for example, I want to search a table for all occurences of project code "1234", then search the list of occurences of "1234" for vendor "XYZ", then sum the invoice values for all occurrences of vendor "XYZ" within the list of occurences of "1234".


Answer

If you have Excel2007, then use SUMIFS like this
=SUMIFS( D:D, A:A, "1234", B:B, "XYZ")

If you have Excel2003, then use DSUM like this
Put the title of project code column in cell F1, put 1234 in F2
Put the title of vendor column in G1, put XYZ in G2
Paste this in H1
=DSUM( A1:E500, "Invoice total", F1:G2)

Assuming, you have the list in A1:E500 with column headers in A1:E1
And you have the column you want to sum named "invoice total"

Do the changes as your table and enjoy it

VBAXLMan is back to feed you Excel needs

Monday 1/19/2009 9:04:24 AM
Mon
Jan 19
2009

Can you help me with my Excel question?

I have people send me a lot of reports that were created through AIX access. I usually take those reports and dump it into Excel, where I do some formatting afterward. A problem that I run across every now and then, is when I hit a multi-valued field and the information is displayed vertically... like so (I will put in which cell it would fall into next to the value)

A1 - [patient 1] B1 - [perscription number 1]
B2 - [perscription number 2]
B3 - [perscription number 3]
A4 - [patient 2] B4 - [perscription number 1]
B5 - [perscription number 2]
A6 - [patient 3] B6 - [perscription number 1]

and so on, and so on...

Is there any way to get those values to move to be displayed on line like so

A1 - [patient 1] B1 - [perscription number 1] C1 - [perscription number 2] D1 - [perscription number 3]
A2 - [patient 2] B2 - [perscription number 1] C2 - [perscription number 2]
A3 - [patient 3] B3 - [perscription number 1]



I appreciate any assistance you can give, and I apologize if I've made my own question sound more complex than it has to..


Answer

Yes, you have two solutions:
1- Using macros, which sounds to me in your situation faster and easier
2- Using formulas, but this will be kind of duplicated formula (or set of formulas)

Now, I want you to answer some questions:
1- How many is the maximum number of drugs per patient you can expect?
2- The formula below, can work in cells C1, D1 and E1 and below, but the problem will be deleting the empty rows, if you answer Q1, then I think I can do that also
Now paste this in C1
=IF( $A1="","",IF( OFFSET( $A1,COLUMN( )-2,0)="",$B2,""))
And this in D1
=IF( $A1="","",IF( OFFSET( $A1,COLUMN( )-2,0)="",$B3,""))
And this in E1
=IF( $A1="","",IF( OFFSET( $A1,COLUMN( )-2,0)="",$B4,""))

then copy those three down

I told you it is a complicated, now these three will transpose your rows into columns
You will still need to delete the empty row, I could delete them using formulas, but I needed more details as I told you

Enjoy my profile, VBAXLMan is back to feed your Excel needs

Monday 1/19/2009 8:44:10 AM
Mon
Jan 19
2009

I want column to change by not the row in excel or google doc?

My formula looks like this

='Activity sheet'!C$1
='Activity sheet'!C$1
='Activity sheet'!C$1

I want it to be

='Activity sheet'!C$1
='Activity sheet'!D$1
='Activity sheet'!E$1
='Activity sheet'!F$1

etc.

Help will be more appreciated.


Answer

Sure, do it like this

Now this will depend on where you are putting it:
So Assuming you want to put this in cell A1 and down, then paste this

=OFFSET( $C$1, 0, ROW()-1)

And fill it down

Yes, I know, I am the VBAXLMan here

Monday 1/19/2009 6:41:27 AM
Mon
Jan 19
2009

How can i sum or count Yes or no in a column and categorized them in Product group im just one formula?

Excel Formula


Answer

SUMIF will do only one criteria, so you will need SUMIFS, but this will work in Excel2007 only
If you have Excel2003, then you can do DSUM

DSUM is criteria based sum, but you will need to build range of cells as your criteria range

You didn't give that much info, otherwise I could do it for you

Mail me here with more info or some sample file, and I can do it for you for free

VBAXLMan is back

Monday 1/19/2009 4:34:55 AM
Mon
Jan 19
2009

How do u get excel to calculate a column of numbers that is the average of successive numbers inthe leftcolumn?

I want (B1+B2)/2 and then (B2+B3)/2 below and so on in the column. Oh ya and please explain clearly and gramattically and speak english!


Answer

Paste this in cell C2
=B1+B2/2
Then copy C2 into C3, C4, C5 and so on

Doing this will give you the average of B2 and B3 in cell C3
Average of B3 and B4 in C4 and so on

VBAXLMan is here to feed your Excel needs

Monday 1/19/2009 1:28:11 AM
Sun
Jan 18
2009

What formula would I use to determine profit or loss in Excel.?


Answer

Having numbers as follows:
A1 has the total amount sold
A2 has the total cost

In A3 paste this
=IF( A2>A1, "Loss:"&ABS( A2-A1), "Profit:"&A2-A1)

VBAXLMan

Sunday 1/18/2009 2:15:06 AM
Sat
Jan 17
2009

Students often tell me that they use excel to figure ?

Students often tell me that they use excel to figure out sales goals for a month and keep track of how much they need per day to make the amount required for their monthly goal. You can use Excel to develop a spreadsheet for this.

How might you do this?


Answer

It is called "Goal Seek", you can find it in Data menu if you are using Excel2003

This Goal seek requires that you have all the cells for data and formulas already set before using

Try it, it is interesting feature

VBAXLMan

Saturday 1/17/2009 6:44:38 PM
Sat
Jan 17
2009

I want to fix a pic in MS Excel 2007 cell and to use in an another sheet with Vlook up formula?

For each cell of employee I want to fix a pic and to use in forms with Vlookup formula


Answer

Excel functions are working with text and numbers
You can not do this with functions, instead you will need macros to do that

I would suggest to do a folder with employee pictures named with employee ids
Then insert a picturebox in that Excel file and do some macros to make it read the selected employee picture.

Something like this:
Sub ReadPic()
EmployeeID =Range("A1").value
PictureBox1.picture=LoadPicture( "D:\Pix\" & EmployeeID & ".jpg")
End Sub

This will read the picture in the picturebox assuming cell A1 had the employee id

I will be glad to help if you just send me the file you are talking about and more details to build the macro for you

VBAXLMan

Saturday 1/17/2009 10:34:54 AM
Fri
Jan 16
2009

Getting an equation from points on excel 2007 ?!?

Alright I have a set of data points that I can graph but I don't know how to label the axis or to get it to derive an equation from a line. When I try to type in my AXIS label its says that the formula I tried has an error all I'm trying to do is label AXIS not put in a formula? what do I do?


Answer

When you click on the chart Excel2007 will show you new 3 tabs at the top right
Go to "Layout"
Then click "Axis Titles"

And do your titles here

VBAXLMan is back

Friday 1/16/2009 5:20:08 PM
Fri
Jan 16
2009

Excel, Update sheets automatically?

Hi,

I have this sheet which i am currently working on - what i would like to do is every time a new record is added to Sheet1, it automatically updates Sheet2.

Sheet1 has 6 categories but every record is just added to the list. Sheet2 has records by categories. It has to be able to find the correct category in Sheet2 and update it with the new record.

Thanks.


Answer

This can not be done in Excel formulas
You will need to do one of the following:
1- Do a Pivot Table in Sheet2 to get the result of each category in certain columns (Might need to do more than pivot table)
2- Do a macro that is refreshed every time you change a cell in sheet1

I can not show you how to do the PivotTable here since it is different between Excel2007 and Excel2003

Also the macro thing cannot be done here, since I need more details from you, but it can be something like this:

- In Excel, Press ALT+F11
- Double click the sheet name (Sheet1 in your case) in the left tree list
- Paste this

Private Sub Worksheet_Change(ByVal Target As Range)
' Macro should be here
End Sub

The macro you need to put here is the macro that I will do it for you for free, if you send me your file to
XL@VBAXLMan.com
with some additional details

VBAXLMan

Friday 1/16/2009 8:13:22 AM
Mon
Jan 12
2009

Can i back calculate a formula using excel?

I have a polynomial function here that i known of the y values and want to derive the corresponding x values. Anyone knows how to do it??

y=-0.0969X^4+342.16X^3-452972X^2+3*10^…


Answer

Your formula is cut

Try to insert spaces between it, Yahoo! Answers cut the rest of it

VBAXLMan

Monday 1/12/2009 12:12:20 AM
Thu
Jan 18
2007

Microsoft Excel: How to show picture if conditions met?

Hi there,

Is it possible to use some kind of formula to show a picture (which is located on another sheet) if specific conditions are met?
For example: I would like to show a green sign if a value is higher than 100 and a red sign if a value is below 100.

Thanks.

Stefan.


Answer

If Conditional Formatting is not enough for you, (It can color the cell based on condition)
Then, it is a VBA
You will need to create a picture image from
Tools > Customize > Control toolbox
and add some macro to load an image based on a cell value

I will be glad to help, if you mail me here in Y! Answers

Enjoy my profile, I am the VBAXLMan

Thursday 1/18/2007 11:01:39 AM
Thu
Jan 18
2007

How to create excel macro to fill in empty cells based on information in abolve cell?

I am looking to write macro that fills in the empty cells in certain range based on info of above info. Idea is to populate cells that have been left empty after pivot table results are copied as value in other worksheet.
It should execute starting from top and going down row by row in selected range.
Example: Row 1 has text "Formula", row two has empty cell. If macro detects empty cell it should populate it with info from cell above (e.g. Formula). It is kind of auto copy function


Answer

It is amacro, right?
So, you will need to create two loops and check if a cell is empty, if so, fill the cell from the above cell, then:

Sub FillFromAbove()
For i=1 to 4 ' column A, B, C and D
For j=2 to 7 ' rows 2, 3, 4, 5, 6 and 7 , so the range is A2:D7
If Worksheets( "Sheet1").Range( "A1").Offset( j-1, i-1).Value ="" then Worksheets( "Sheet1").Range( "A1").Offset( j-1, i-1).Value = Worksheets( "Sheet1").Range( "A1").Offset( j-2, i-1).Value ' to fill from the above cell
Next j
Next i
End Sub

Wow, I am realy the VBAXLMan

Thursday 1/18/2007 8:52:04 AM
Sun
Jan 7
2007

Excel formula to make input number its inverse?

I want to be able to input a number in a cell and automatically have it convert to its inverse (in that same cell). Is there a forumla for that?


Answer

No formula can do this.
Formulas are have to be entered in a cell first to work.
So You will need a VBA to do this
put this VBA macro in the code area of any sheet you want to do this:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveCell.Value = 0 - Val(ActiveCell.Value)
End Sub

need more info? mail me here in Y! Answers

Enjoy my profile, I am the VBAXLMan

Sunday 1/7/2007 9:07:50 PM
Wed
Dec 20
2006

Matching with Ms Excel?

I have a spreadsheet which contains 5-6 columns of info, with about 200-300 rows. Column D contains totals. Some of the totals in column D will match with one another, just with a minus sign. Say if there is a 54300, then there is another -54300, just its with a minus sign (this is actually a ballance of a clearing account).
What I want is to write a formula or macro that would take column D value from the first row, search through all column D, find matching value, cut those two rows and paste in another sheet within the same file. Then repeat this with all the rows, cutting rows with matching value in column D, so that all the matching values are pasted one below another in another sheet. This way the original sheet will only contain unmatched items.
I work as an accountant so have to go through account statements like that very frequently. Having a formula or a macro like that would make my life a lot easier :)

Thanks for any responses!

Happy Xmas!


Answer

You can solve it in one of these ways
1- Formula
2- Macro
I can post here the formula solution, because it is easier to understand.
Now, leave column E as seperator, go to F2 and paste this
=ABS( D2)
and fill it down to all the other cells. In G2, paste this
=LARGE( F:F, ROW()-1)
And fill the G2 down to all the cells below
Hay, it is your exact list, but sorted in Descending order

Yes
That is because VBAXLMan show you how to do it

You can cut and paste this G column cells to what ever you want

Enjoy it and..

Enjoy my profile, I am the VBAXLMan

Wednesday 12/20/2006 5:36:13 PM
Wed
Dec 20
2006

I have a problem in writing Excel formula?

Hi guys,

I have a problem in writing an excel formula .. IT looks very simple.. I wanna say that if the value in a specific cell is less than 1000, add 30 ..

I have tried =if(K5<1000,"30").. it never work


Answer

Yes do it like this

=IF( A1<1000, A1 + 30, A1)
This will add 30 to the cell in A1 if it is less than 1000 and bring the origin cell value if not

Enjoy my profile, I am the VBAXLMan

Wednesday 12/20/2006 10:11:54 AM
Wed
Dec 20
2006

I have data in a column in an Excel spreadsheet and I need to use it in formulae in a row on another sheet.?

I don't want to have to type the formula over and over for each cell! Is this possible? Using the '$' sign doesn't work...


Answer

You can use
1- The Copy + Paste Special (Transpose)
2- Use the COLUMN(), ADDRESS functions like this:
Your source column is in D in Sheet1, you want it in row 5 in sheet2, so in cell A5 in sheet2 paste this
=INDIRECT( "Sheet1!D"& COLUMN())
the function in cell A5 will call the value in cell D1 in sheet1 and so on.
You can change the sheet name of the source column by changing Sheet1 in the formula.
If you are not starting from D1, or you want the row to start from other than A5 then add the -1, +1 after the COLUMN() to modify it

3- Use the TRANSPOSE function

I think this will answer you

Enjoy my profile, I am the VBAXLMan

Wednesday 12/20/2006 4:36:07 AM
Sun
Dec 17
2006

Is there any formula to convert numbers to words in "Excel Program" other than Add-ins. Pls answer me.


Answer

Microsoft do have a function like this, it is a macro, so you may need to copy it first to your VBE module window

Check this out
http://www.ozgrid.com/VBA/CurrencyToWord…

Enjoy my profile, I am the VBAXLMan

Sunday 12/17/2006 9:43:26 PM
Tue
Dec 12
2006

This is regarding Concatenate function in Excel:?

I am trying to concatenate a Currency value ( $ 233,456,389) with a

percentage ( 74 %) in Excel using this function :


=CONCATENATE("$ ", TRUNC(B313)," @ ",TRUNC(C314)," % ")



I would like to have it as $ 233,456,389 @ 74%

Instread I am getting $ 233456389 @ 74% (currency value without commas)

Can someone help me in getting the commas in the currency display ?


Answer

Add this to the TRUNC function
TEXT(B313 ,"###,###")
Means the TRUNC would be
TRUNC(TEXT( B313, "###,###"))
and your final function will be

=CONCATENATE("$ ", TRUNC(TEXT( B313, "###,###"))," @ ",TRUNC(C314)," % ")

Enjoy my profile, I am the VBAXLMan

Tuesday 12/12/2006 11:03:16 PM
Thu
Mar 30
2006

In excel,when I try to enter formula it is not applied on the cell.Pls suggest me?

Excel


Answer

Sometimes, Excel do autoformat to the result if one of the references are date or if the result can be converted to a date, so just go to Format--> Cells and make sure in the Number tab the Number value are selected from the list.

Thursday 3/30/2006 2:34:14 AM

Tags

History