Building a formula

MS EXCEL

    Sponsored Links

    Next

  • 1. file won't open!!!
    i am using excel mac 2004 on a g5. while working on the file the program quit. now i can't open the file!!! but i can open the worksheet in word. someone please give me some insight.
  • 2. Macro Command Copy() doesn't work in OSX Tiger
    I have just started trying to move my files to OSX Tiger. Immediately the macro command Copy() doesn't work. It is trying to work on 2 columns by 51 rows of a CSV file. I have tried saving the file to excel. Interestingly Copy() does work earlier in the Macro. ANy thoughts/help much appreciated.
  • 3. Displaying Referenced Cells
    I just switched from PC to Mac. On Excel for PC, selecting F2 would create a colored border around the cells that are referenced within the active cell. Does anyone know the Mac analog for this F2 function? Thanks, Michael

Building a formula

Postby B_David » Sun, 11 Jan 2009 05:09:29 GMT

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

How do I build a formula that increases the value in a cell by 60%. For example, if the amount in the cell is $100, and I want to increase this amount in the cell by 60%, the amount in the cell would now be $160; not $100. How do I create this formula so I can copy this down to all the cells in the column?

Re: Building a formula

Postby Bob Greenblatt » Sun, 11 Jan 2009 06:20:23 GMT

On 1/9/09 3:09 PM, in article  XXXX@XXXXX.COM ,



First you need to understand that a formula can only change the value of the
cell it is in. It can not change the value of another cell. So assume that
cell A1 contains $100. This formula in cell B1 will increase A1 by 60%:
=1.6*A1
You can then fill this formula down as needed.

-- 
Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom


Re: Building a formula

Postby CyberTaz » Sun, 11 Jan 2009 10:08:24 GMT

Another option if this is a one time thing - Type 1.6 into a cell, copy the
cell, then select the range of cells containing the values you want to
increase by 60%. Go to Edit> Paste Special, select Multiply in the Operation
section, then click OK - the values in the selected cells will be multiplied
by the 1.6 [increased by 60%].

HTH |:>) 
Bob Jones 
[MVP] Office:Mac



On 1/9/09 3:09 PM, in article  XXXX@XXXXX.COM ,





Similar Threads:

1.Build excel formula using field values as text in the formula

Instead of writing the formula to add two cells as say, B4+B38 in Excel, I 
want to build the formula using the contents on another cell, e.g. 
I've tried B4+"B"&(TEXT(B13,0)). 

The value of B13 is 38, therefore +"B"&(TEXT(B13,0)) equates to B38. That 
works fine. But if I combine it with B4 as above, the result is #VALUE!
Any ideas on how I could get around this?

2.Build a formula from contents of other cells?

Greetings

I don't think it is possible to do the following:

cell A1 contains: =Sheet2!
cell A2 contains: G
cell A3 contains: 3

cell A4 contains: +A1+A2+A3 

to produce the formula '=Sheet2!G3'

But is there another way to achieve this so that I can 
regularly change many formulas by changing the contents of 
one cell?

thanks
Dan

3.Can you build a formula in one cell with flexibility

I have a simple division equation in one cell.  It calculates from two other 
cells.  The two cells may have to be moved down (not the entire row, just a 
portion of row). But the cell with the calculation follows this shift.  I 
need for that calculation cell to be smart and after I shift the cells that 
make up the calculation, I need for the original cells to still be part of 
the calculation cell

4.build complex formulas using the dialogue box

In earlier versions of Excel, such as 97, you could create complex formulas 
by using the formula dialogue box for help.  There was always an insert 
formula icon next to the criteria line currently being populated.  This would 
allow you to keep building complex formulas without having to know the 
criteria for each formula.  Why isn this possible with 2002/2003?  I hope 
they bring it back.  Does anyone know of a work around for this?

5.named ranges in building excel formulas from a string

I have two ranges named PReq and QOutput. 

I have this procedure: 


Sub test_dependency() 
Call dependency(Worksheets("PP"), [PReq], [QOutput], "testtitle") 
End Sub 


And I have: 


Sub dependency(Result As Worksheet, Ratiorange As Range, Qrange As 
Range, Title As String) 
row_id = some array which results in a value 
[Yearstart].value = 5 
rowitem = 5 
colitem = 5 
z=10 
Result.Cells(rowwriter, z).formula = "=vlookup(" & row_id(rowitem, 1) 
& "," & Qrange.name & "," & z - [Yearstart].Value + 1 & ",false) * 
index(" & Ratiorange.name & "," & rowitem & "," & colitem & ")" 


I desperately want the formula to look like: 


=vlookup(4,PReq,6,false)*index(QOutput,5,5) 


I'll take 


=vlookup(4,PReq!$b$5:$h$100,6,false)*index(QOutput!$b$5:$g$10,5,5) 


but I get 


=vlookup(4,=PReq!$b$5:$h$100,6,false)*index(=QOutput!$b$5:$g$10,5,5) 


I've tried Qrange.name, .address, .value, .text anything. they all 
dont' work. Then I tried to go through the Locals window and when I 
clicked onto Qrange -> Cells -> Name, lo and behold, it was "QOutput". 
So then I tried 


?Qrange.cells.name in the immediate window and got a syntax error 


same with: 


?[Qrange].cells.name and ?Range("QRange").cells.name 


Tell me your learning curve is as steep as mine because this should be 
very obvious 



6. building a formula

7. building a formula with non-US country setting

8. Building Excel Formula that Returns the Column of Last Cell with Data



Return to MS EXCEL

 

Who is online

Users browsing this forum: No registered users and 47 guest