Questions about using in-cell functions/formulas in spreadsheet software.

learn more… | top users | synonyms (5)

0
votes
1answer
19 views

Excel TODAY() function

I have two tables in excel The first table is called Oranges, it shows where the oranges were produced and where they were exported to: The second table is supposed to show the total oranges ...
2
votes
2answers
48 views

Creating an Invoice in Excel

I have only dabbled with Excel and not much in quite some time now. I have an Excel Invoice laid out so Column A is a name of a job and each row in column A can be a different job. Now column B ...
-2
votes
1answer
30 views

# of days in a month based and start and end dates

This question relates to Calculate Total Number Of Days Per Specific Month. If you look at the original question what IF you have a start and end date Jan - March and you need to know how many days ...
0
votes
0answers
28 views

Excel formulas linking sheets in workbook

I am looking to automatically update a results sheet by moving down by one row every time I input a set of data in another linked sheet (raw data) in the same workbook. I will be overwriting the Raw ...
0
votes
0answers
15 views

Aggregate values by month in Excel [duplicate]

For each row, I need to aggregate values by month. Date Jun Jun Mar Jun Name1 10 11 14 12 Name2 2 34 4 7 Need Result Date Jun Mar Name1 33 14 Name2 43 4
1
vote
2answers
33 views

Need SumIf based in two Criteria

I Need Help I need a SUMIF, when name and date are match. Date Jun Jun Mar Jun Name1 10 11 14 12 Name2 2 34 4 7 Need Result Date Jun Mar Name1 33 14 Name2 43 4
0
votes
1answer
7 views

Display empty cell if referencing empty cell (formula)

I'm working on an excel spreadsheet, and im aiming for a clean interface with almost all cells filled with specific formula. Ex formula on column V : =IF(G1=" LNN ","PENDING", "N/A") What's ...
3
votes
2answers
25 views

Drop Down List and one formula

I am trying to get an average depending on two drop down lists. But i would like to get an average even when the second drop down has no input in it. The formula I use is a simple: AVERAGEIFS with: ...
0
votes
1answer
32 views

Is there a formula to look for partial text in a cell?

This is what I'm using right now in the formula: =IF(E6="Mix Design Gr S","By Mix Design","")&IF(E6="R-Value","By Soils","")&IF(E6="PS Package","By Soils","") Is there a way to only look for ...
0
votes
2answers
33 views

How to extract the string from between the Nth and N+1th occurence of a character in Excel 2010?

I have a spreadsheet with a column that contains a list of one or more database names. Unfortunately, the turnaround time to get this report altered is long. So I need to manipulate the data. The ...
0
votes
2answers
23 views

Range mapping in Excel

I have a column of data in Excel with numbers [0..100]. I need a new column, which maps these to a new set of values such that: > 90 : 4 80-90 : 3 70-79 : 2 60-69 : 1 < 60 : 0 I attempted to ...
0
votes
1answer
23 views

Trying to create sequential hyperlink to worksheets in the same book

I have a summary page, and 50+ other pages, numbered 1-50. what I'm trying to do is create a hyperlink that will go to the correct sheet when clicked. I've got it working for one, but when I drag it ...
0
votes
1answer
25 views

How do I convert sql to excel syntax?

I have two sheets in an excel file, "sheet1" and "sheet2". The first sheet has a table called Oranges, it shows where the oranges were produced and where they were exported to: On the second sheet ...
1
vote
1answer
28 views

How to Calculate Total Less Duplicates?

I am attempting to create a formula that calculates deviations from a certain parameter threshold in hours. The parameters being recorded are water flow in gallons per minute and chemical addition. ...
0
votes
1answer
37 views

Using libreOffice Calc function in macro

I am trying to create a basic macro function in calc to perform a hexadecimal to binary conversion with more then 10 bits. But I've got a lot of troubles. First, where can I find actual ...
1
vote
1answer
34 views

Excel: Look up value in other sheet

I'm looking for a solution to a little problem I'm having with vlookup and index/match. The problem I'm having is that the lookup value is from a cell containing a formula. If I punch in the number ...
0
votes
2answers
45 views

EXCEL VBA works with F5 - but will not run automatically when cell values are changed

The VBA formula works perfect with F5 Column E cells E3:E5 have Functions that calculate to a dollar amount or $0.00. This is the formula pulling from =SUM('MEDENT Proposal - Creator'!B15*15) ...
0
votes
1answer
22 views

EXCEL automatic Tab Color change not updating

I have a formula for Automatic Tab color change that works when I manually change the cell, but it does not work when the cell is changed by a function updating it from another sheet: =SUM('MEDENT ...
0
votes
1answer
48 views

VBA Code for SUMIF statement - Need to build-in additional conditions

I'm trying to fumble my way through some VBA scripting. I found this code online that allows for a SumIf function to occur when a single condition is met. I would like to expand it to two maybe three ...
0
votes
0answers
41 views

EXCEL formula - Runtime Error 28

I have a VBA that works - but leads to a Run Time Error 28 after a while. Colum B3:B11 have Hide or Show from a function in them =IF(E3=0,"Hide","Show") E3:E11 has a dollar amount or 0 from another ...
3
votes
4answers
50 views

How to merge multiple columns in Excel so that unique values are retained in the new column?

Say the data are in Col1 and Col2 below. How can we create Col3 by merging Col1 and Col2? Col1 Col2 Col3 ------------------ a b a b c b c d c g e d ...
0
votes
0answers
26 views

PHONETIC function doesn't work in Excel

I stumbled across the PHONETIC function, which apparently is supposed to generate furigana readings for Japanese kanji. So if I put in 東京都 to Cell A1, and in cell A2 I write: =PHONETIC(A1) It ...
1
vote
1answer
37 views

Carrying a conditional formatting formula from one reference row to another reference row

I have month wise reference row to use conditional formatting. They are arranged like Target Date as reference row and actual dates are arranged below the target date suppose it contains the actual ...
0
votes
0answers
23 views

How can I compare a student's first name, last name, and day of detention to identify what lunch period they have in google spreadsheet?

I have a google spreadsheet that auto-fills last names in column B, first name in column C, and day (not date) of detention in column F. In another set of columns I have the students' lunch period ...
-1
votes
0answers
18 views

Combining non identical excel worksheets [closed]

I want to combine data from two Excel worksheets. Each has a single piece of (different) data (a percentage and a weight) relating to patients who are identified by their unique hospital numbers. ...
0
votes
1answer
31 views

what is an appropriate shape name as my routine does not say?

i have the following routine that i found on the net here: http://www.ozgrid.com/forum/showthread.php?t=77662 this routine isn't working cause i dont know that shape name. can anyone tell me what the ...
0
votes
2answers
55 views

Find the number of cells to sum to reach a certain amount

I have a column of values (they are ordered if that matters): A:A. I have two values: a starting value B1 which is also present somewhere in A:A, and a goal value C1. Let's say the we can find the ...
0
votes
0answers
21 views

How can I speed up functions in Excel? How I can make my sheet/worksheet faster? How do I know how long took create something in Excel?

I have long functions in Excel that use other sheets in my worksheet for example : {=INDEX( Sheet1, MATCH( $A2 & MAX( IF( ( 1 * ( $A2 = ...
0
votes
1answer
16 views

Add a number above a cell and continuing on in excel (example given)

I am looking for a formula in excel to add the cell above to the current cell. Then in the cell below, add the current cell and the one above and so on. I am currently doing it manually and it is ...
3
votes
3answers
49 views

Finding previous row with a given value

Let's say I have a table with three columns (order is important and can't be changed), rows are sorted by A: A B C date ID last_date 10/05/2015 ...
3
votes
1answer
70 views

Work out Start Time + Hours with working days and times

I have the following problem: In G4 I have 19/01/2016 10:00 In F4 I have the value 200 (units) In E4 I have the value 38 (minutes) I want to calculate G4 + (E4*F4) i.e. 19/1/16 10:00 + 7,600 ...
-1
votes
1answer
31 views

how to find a changing value in a row, and get a value in the same colunm

I am trying to create a quick page to view a schedule from a list of teams and dates and locations. The format of the dates and locations do not change, however the teams (home and away) do. So ...
1
vote
1answer
28 views

Excel: sort according to example row

I am using Excel 2013 on Windows 7. I have 2 columns of data like this: Name Value aaa 1 bbb 123 ccc 81 ... ... I need to sort this data into specific order that looks like random so I cannot ...
1
vote
2answers
149 views

Changing Excel columns & rows , from vertical to horizontal order

Currently I have a list of computers with their hardware specifications in a vertially order, this should be changed to horizontally. What I have is: | A | B | C | | PC-NAME | ...
1
vote
2answers
46 views

how can i create alphanumeric user id on excel sheet?

The user id must have first three letters of the user first and last name as well as random number as shown below- User id Fname Lname Langu AA_RoeDuT449 Roelof DuToit ...
0
votes
1answer
27 views

Filter list with condition and return results as delimited list in a single cell

I need to return all values in column A that match a set criterion in Column B. The returned values need to be in a single cell in a single formula. In the attached Sample image, I need a single ...
0
votes
1answer
42 views

MS Excel 2010 merge two sheets

I have an inventory I do each month and have a fairly simple question but not sure what is the best way to complete this task. I have 3 columns. ItemNumber, ItemName, and TotalQTY. The first sheet is ...
1
vote
2answers
19 views

Excell: Which values to increment and which to not in autofill

I am trying to make a word encoder that has a column in which you input letters and a column that will be referenced to in the function that detects the letter and encodes it. this is my function. ...
1
vote
2answers
54 views

Excel formulas for splitting and combining IF, AND, MIN

I am having some issues with a couple of formulas in excel. I have three rows of data for three people that are splitting the values equally until two of the people meet a max number. then the values ...
1
vote
1answer
16 views

Flatten/Concatenate an array to a list for match in excel formula

I have a table of data in excel which has double header rows. I would like to perform an hlookup against this table using a flattened version of the two headers. For example, if my data were like ...
0
votes
1answer
24 views

Is it possible to use index and match while adding values? [duplicate]

I'm using this formula in my main worksheet in cell N3: =IFERROR(INDEX(Totals!M$3:M$33,MATCH($B3,Totals!$K$3:$K$33,0)),0) Details: $B3 lists first/last names on main worksheet. Totals!$K$3:$K$33 ...
0
votes
1answer
22 views

Excel, count characters except a string

I'm using Excel 2013. Basically I'm trying to count the number of characters within a cell, but if the cell contains a specified string to ignore that string but count the rest. Looking at the ...
4
votes
2answers
798 views

Excel “And” and “Or” Command

I have taken notice that the "And" and "Or" command is not consistently give the correct answer. 1) "And" formula =if(And(B2,C2>3), "Yes", "No") input Cell B2=4 and Cell C2=3, yield answer "No" ...
2
votes
1answer
35 views

Create shared library of VBA functions in Excel

At our company we have a lot of different Excel files to keep track of our projects. We would like to implement some VBA functions in these files, written by us, the IT department. We want it so that ...
0
votes
1answer
14 views

How do you obtain an array of vlookup values without an extra column in Microsoft Excel?

I have a list and a lookup table. The list will be in column A and the lookup table will be in columns C and D. Each cell in column C contains text and is unique. Column D has a corresponding value ...
0
votes
2answers
37 views

Random selection in Excel based on some criteria [closed]

I am a beginner .. I need some help to write a formula in excel urgently to randomly select people to 6 teams based on gender for example: I have a list of 78 people containing 48 females and 30 ...
0
votes
0answers
32 views

How to keep a Value in Excel (constant value)

I have set up a formula, =IF(T74="1/2Dry Stack",0.1,IF(T74="DryMortar Joint",-0.1,0)) Linked to a drop down list on another sheet. How can I keep the value of the formula after making another ...
1
vote
1answer
42 views

Excel Top 5 Formula Explanation

This formula below works wonderfully! But I am having some trouble wrapping my head around what is going on. Can somebody explain what is happening here step by step. I have a good knowledge of ...
2
votes
1answer
49 views

EXCEL Formula economies of scale calculation

I need a formula to calculate a subscription charge that reduces based upon the number of subscriptions purchased, but as additional different subscriptions are added they will be at a different ...
-1
votes
1answer
13 views

How can I parametrize an Excel Sort, i.e., make a generic sort? [closed]

I am trying to build an Excel sort command which allows me to specify, as parameters (arguments) row numbers, columns, and worksheets. I can build the strings in the command, but have not been able to ...