A couple of alternatives Excel Formula Help


    Sponsored Links


  • 1. Formula in Excel--SUMIF
    I have results from surveys we sent out and I need to make a database of the results. I am more familiar with Excel than Access. We have questions that the responder selected an answer of H, M or N. I need to be able find a formula to find out how many H's, M's and N's there are once the database has been entered. Any suggestions! THANKS! -- Richelle
  • 2. Tranfer Multiplan files into Excel2003. How?
    Help. I need to know what I need to do to load some old speadsheets in MS Multiplan into Excel 2003?
  • 3. How do I copy a conditional format that uses a formula in a serie.
    I have an Excel spreadsheet in which I set up a conditional format that changes the color of an entire row when one of the cells has a value greater than 0. I want to copy this format throughout the sheet. I can't find a way to do it except by formatting each row individually. Every way that I've tried does not allow the formula to be in a series which means that when there is a value in the first cell the entire sheet changes color. Any ideas would be appreciated. Thanks.
  • 4. How can I remove previously used footers from footer selections?
    When in Excel and viewing the header/footer, I do not want to see previously used footers in the dropdown. Does anyone know how to clear the previously used footers from the dropdown selection box?
  • 5. How can I enter information in a different cell in one Excel shee.
    I'm trying to set up a spreadsheet that I have to update frequently. I want to be able to do multiple entries on one sheet and each time I enter an amount in one sheet, have it update on a master sheet. For example, if I enter something in cell C1 on sheet 2 one day, I want it to show up on cell C1 on the master sheet. But the next day when I have to enter an updated amount in cell C2 on sheet 2, I want that amount to update on cell C1 on the master overlapping what was previously entered. I want to set it up so that even if I have 10 entries, each entry I make will overlap & update the amount in cell C1 on the master sheet. Does anyone know of a formula for this or if it is even possible? I would really appreciate everyone's help.

A couple of alternatives Excel Formula Help

Postby Hilken » Sat, 28 Feb 2004 01:07:07 GMT

I opt for formulas when possible so I do NOT have to worry 
about any changes directly or in links. 

First alt.
Add a column for each quarter
you can put the start (SDay) and end (EDay) dates for
   each quarter in two cells the 2nd and 3rd row of
   each column or elsewhere for easy annual changing
   or directly in the formula.
in the first data row of each quarter's column
    =IF(AND(Ax >= SDay, Ax <= EDay), Fx,"")
    double quotes "" is the null or blank character =
        No zeros
Cells for each column's (quarter's) total can put as
       If at the beginning or end of the data, you can
          hides unwanted rows to print a quarterly report
       AT the head allows easy view of quarter totals

Second alt.
Select all applicable rows
Sort by column A "Date"
add a total cell for each quarter range as desired.
      =sum(Fx:Fy) where x, y start end rows for each
copy formula cells down as needed
    If using cell references for the dates lock the
         reference by pressing F4 key or $col$row
         before copying
Note: after testing you formulas, delete all data,
    name the sheet "template" or such and then copy it
    in the same book (or another) and name it for the
    year + other reference if needed. 


Similar Threads:


A couple of simple questions. I'd appreciate some help.

1)How do I get a UserForm to open automatically when I open a
worksheet in Excel.


Can someone supply me with a VBA formula to locate the next empty cell
(or row  or column) after entering data in one row to go to the next
for input. The code in the books I am referring to just plain doesn't

                           How do I get a command button on a
                          on sheet1 to automatically open a UserForm
on sheet2 to
                          enter new data.

2.Alternative formula to the vlookup formula?

I have data arranged like this:
a              b                               c               d             
 e             f
Part No.	price		xyz1	xyz2	xyz3	TOTAL
abc1	1		3	1		xxx
abc2	2			2	1	xxx
abc3	3		1		3	xxx
xyz1	.4
xyz2	.5
xyz3	.6

Part abc1 is assembled with 3 pieces of xyz1 and 1 piece of xyz2.  I would 
like to have the cells under "TOTAL" give the total price of that part 
including the assembly components so for part no. abc1, I want the price to 
show $2.70, part no. abc2, to show $3.6 and part no. abc3 to show 5.2.

I am currently using a =sum(vlookup*c3, vlookup*d3, vlookup*e3) formula 
which works great except that there are so many assembly items that I cannot 
add any more functions to the cell.  Is there a different forumla which would 
allow me to find the total price?

If you need additional information, please let me know.



3.help with excel cell formatting - alternative aproach

If you leave the column of data as it is you can use a modified average 
function to ignore the Zero values


note it is array entered - Ctrl Shift Enter

a cleaner result IMHO


>In F5 i have the formula SUM(D5*E5) the cell is formatted for currency. 
>i drag my formula it creates  in every cell $0.00 this is affecting my 
>average at the bottom.  How do i keep the formula but loose the $0.00?
>Thank you for your help!

4.Alternative program to Excel for array formulas

We're using a quite powerful machine: quad core extreme CPU, 4gb of Ram, XP 

However, we're dealing with array formulas which refer to a worsheet of 
205,000 rows * 23 columns (ever-increasing). 

Excel doesn't operate properly and on manual calculation, it shows the 
"Excel ran out of resources". 

Is there another way to calculate such large an amount of information?
Another program that is?

5.I need alternative formula for SUMIFS in Excel 2003

I am using the SUMIFS formula in Excel 2007, but Excel 2003 doesn't 
recognize this and I need to send the file to people using Excel 2003. I 
tried to use SUMIF, but I have 3 criteria to search for.
Does anybody know of a similar formula that works for Excel 2003?


6. Couple of Questions Need Help Please.....

7. Help ! a couple of newbie questions


Return to MS EXCEL


Who is online

Users browsing this forum: No registered users and 82 guest