Home

Formulas and Functions

555 Conversations

Latest Activity

Custom List Message Item

Hi all,

 

I'm in a bit over my head with this at the moment..

I'm trying to keep track of pallet storage costs. To this end I would like to have cell D2 = sum of the cells in column C (2nd image below = seperate sheet) but only where column A = dates betw

... Read More
44 Views
4 Replies

Erran,

 

I have attached the solution for you.

 

Hope that helps.

Good morning, everyone,
I use the SI function for a point calculation based on a result. the cell referring to the computation string is on another sheet of the workbook (Points). I want to copy this formula in several lines but at each line the reference

... Read More
32 Views
2 Replies

Hi,

 

Do the following:

  1. Go to the formula.
  2. Double click on each cell reference and press F4, so you get a dollar sign before each part of the cell reference like this: $
... Read More

Hi Vincent,

 

To fix the cell(s) use absolute references instead of relative one (or mix)

https://support.office.com/en-us/article/Switch-between-relative-absolute-and-mixed-references-dfec08cd-ae65-4f56-839e-5f0d8d0baca9

Read More

Hi, hoping someone can help me.

I am trying to make a sheet that reads analysis-results for me, i have different parameters within different intervals.
The result varies between different intervals - giving them a surtain "class".
F. eks, my result is 12.
The

... Read More
42 Views
1 Reply

Hi Elisabeth,

 

I'd suggest not to use strings to define the ranges but upper limits for them like

RangeData.JPG

When the formula which returns in which range the result is could be

=
... Read More

Hello All,

 

I need to be able to extract values vertically and display them horizontally. In the attached example, I have multiple instances with the same part number but with different prices. I want to be able pull those different values (prices) verti

... Read More
25 Views
1 Reply

Hi Michael,

 

Not sure about your formula, bit hard to recognize from the picture. Alternatively you may try as on this sample

SmallHV.JPG

=IFERROR(AGGREGATE(15,7,($C$2:$C$10)/($A$2
... Read More

In a cell in my excel sheet, this function is used

=INDEX(Bereich_Daten;AB3-3;4)

Can someone explain, what does this mean?

Thanks in Advance.

Regards,

kavin.

 

Index.png

 

Read More
31 Views
1 Reply

Hi Kavin,

 

I guess first parameter is the name of the range/table in your workbook, you may check in Name Manager what's that. Second and third are row and column number

... Read More

Hello,

 

I have a formula that returns an array of values, and what I'd like to do is expand the formula to take that array, perform a vlookup or index/match on each value in the array to return another array, and then sum that second array.

 

Is this pos

... Read More
24 Views
1 Reply

PS - I know I can sum an array simply using the sum() function, so this question is really about performing the lookup on an array, in an effort to return a second array.

Read More

Hello,

 

Im in need of help.

 

I have a worksheet with three tabs.  Tab A is my working tab.  I need to perform a VLOOKUP/IF Formula to find a work task 'Maths' for example in a Column B, in 'Tab C' and pull the who row of data to Tab A

 

I also then n

... Read More
17 Views
0 Reply

Trying to balance a sheet in a number value with (8) digits behind the decimal... using sum totals that derived from fractional calculations.  ie... =sum (1/2*3/4*2/3*1/5*1/2).  Continue to get results .99999978 or something. How can I get it to balance?

... Read More
30 Views
1 Reply
Hi Susan,

When you say balance, do you want to ROUNDUP?

Or perhaps another way to look at it would be how to make a formula result static...

 

The problem: I have created an  invoicing/booking solution for my business using excel sheets, I have many sheets such as delivery addresses, client data and addresses,

... Read More
32 Views
1 Reply

Hi Esat,

 

It does sound like you'd be better off with some VBA to copy and paste historic transactions as values into a table / database.

 

It's a difficult query to ans

... Read More

 In column A, EID, I want the employee's SSN (Column D) to show up at all times.  So in A4 (Alice Jones), I want her husband, who is the employee, Jim Jones' SSN should appear.  Same for child.  Is there a formula for this?

 

  A B C D E
1 EID Last N
... Read More
27 Views
1 Reply

In the example provided you can get it to work via the Last Name column, but if you have more employees with the same last name it will not work. You need extra data.

Read More

I am trying to sum by color across multiple sheets. 

I currently have, 

example:

 

=sumbycellcolor('sheet1:sheet7'!b15:b27,b14)

b14 being the color

ranged between b15:b27

but i have marked error...

anyone know how to fix this?

Read More
41 Views
3 Replies

Hi,

 

There is no function in Excel called (sumbycellcolor), you have to use third-party solutions to get this functionality! Follow the below links:

Sum By Color and Count By Color using VBA in Microsoft Excel

... Read More

I can find no help on how to tell Excel to properly format and calculate dates before 1900.  I want to calculate age at death and have birth and death dates in columns.  But Excel does not recognize March 30, 1894.  It calls is YYYY = 3794.  And subtracti

... Read More
23 Views
1 Reply

John,

 

I can offer you formula #1 on excelformeln.de:

Difference of dates before 1900 / a.c.n.

Read More

I'm using APIs (PowerQuery, Excel 2016) to get financial data from web that returns decimal separator as period ("."), but I need to convert it to non-English localization comma (","), which is my system default and output requirement.

 

API input from web

... Read More
76 Views
4 Replies

Hi,

 

I have a complex formula that I need to write involving dates.

 

I have students that are handing in an assignment.

 

I have a due date column.

 

I then have "grace" period of one week. After this for each week (period) it is overdue they receive a point (t

... Read More
72 Views
5 Replies

Hi Nicole,

 

It depends on how do you calculate weeks - is that calendar 7 days or you exclude public holidays and/or weekends; if the exam passed in 27 days from due date

... Read More
Hello, i have instructions to replace any value that is 0.0% or less with a dash (-). Is there a formula to correctly format this? I do not want to lose points for simply manually entering the dash symbol into all of the cells that require a dash. Thank y... Read More
29 Views
1 Reply

Hi Dakota,

 

Just follow the below steps, but these steps are applied to Excel for Windows, and it's not very different in Mac.

 

Step 1

Highlight the range of cells and press

... Read More

Hi. I am building a spreadsheet that can add how many days my coworkers have been active, sick, etc. I created scroll down lists with each status, and created 31 sheets for the month. A final sheet adds everything for a monthly summary. However, I cannot

... Read More
35 Views
0 Reply

Hello,

I am new to using spread sheets on Excel and am wondering how to enter the formulas to achieve the sum at the right of the columns and also at the bottom of the rows.

For an example I have column C as a credit D as debt and a few more as labor debt a

... Read More
45 Views
1 Reply

Dear Barry,

 

I advise you to take an essential training course in Excel such as this paid course:

Excel 2016 Essential Training

 

You can search for some free courses on YouT

... Read More

I got a test file with 10 companies. I want to calculate the median by combining TA and ROE. The objective is to find the 5 companies closest to the target company in terms of TA and then the 3 companies from those 5 which are closest in terms of ROE! I a

... Read More
34 Views
0 Reply

similar question than last time. I'm trying to calculate the median P/E ratio of the 20 firms that are closest to the firm to be valued in terms of total assets.

I got all my company (S&P 500) tickers in column C, the respective total assets in column I, a

... Read More
138 Views
12 Replies

Got one more question. Now I want to combine TA and ROE. The objective is to find the 5 companies closest to the target company in terms of TA and then the 3 companies fr

... Read More

Moritz,

 

=AGGREGATE(17,6,$Z$4:$Z$20/($B$4:$B$20<>B4)/($I$4:$I$20>=I4*0.9)/($I$4:$I$20<=I4*1.1),2)

Hi,

 

In Excel I have a column of "if" statements where the results are numerical values.

 

When I go to another column and insert =sum(C4:C78) I get a zero answer.

 

How can I add up the results in the new cell?

Read More
50 Views
2 Replies

Hi, 

 

Your problem is:

These numbers are considered as texts, so SUM ignored them, and returns 0 since there are no numbers to add!

 

You can check that by using ISTEXT funct

... Read More
Best Response confirmed by Nicole . (Occasional Contributor)

Hello,

I am trying to get at some cross buying behavior from a large data set that I have. Attached is a simple bit of sample data and then my desired output. There is a ton of data in my actual worksheet so a formula (based off of either the Pivot Table o

... Read More
45 Views
2 Replies

Chas,

 

not the best solution.

 

Define named ranges:

Brand_A ='Sample Pivot Table'!$C$3:$C$5

Brand_B ='Sample Pivot Table'!$D$3:$D$5

Brand_C ='Sample Pivot Table'!$E$3:$E$5

Bran

... Read More

I am trying to count the number of calls I receive to a specific phone number based on the area code from which the call came. I have multiple 800 numbers for my office and therefor I need to know how many calls each 800 number gets based upon the state (

... Read More
49 Views
2 Replies

Hi Benjamin,

 

Much better to organise your data into the tables.

With range like on your screenshot the formula could be like

=SUMPRODUCT(($B$2:$B$100=$B2)*(COUNTIF($G$1:$G$
... Read More

I need some (a lot) of help with getting information from one workbook to another. Both workbooks have a common column of item numbers. workbook1 is a master file with all available item numbers and each item's unit cost. The item numbers in workbook2 are

... Read More
40 Views
1 Reply

Hi Patrick,

 

All you need is a straightforward VLOOKUP!

If you don't know how to use VLOOKUP, you can follow this link to get started with it:

VLOOKUP Function

Read More

So, I have created a formula with multiple "COUNTIF" functions over varied ranges.  However, for a few cells within the formula, I only want the formula to count one cell or the other, but never both.  So, for example, =COUNTIF(E17,">0")+COUNTIF(E24,">0")

... Read More
34 Views
2 Replies

Hi Chris,

 

If we speak about single cells, not ranges, that could be

=--OR(E17>0,E24>0)

 

I am in need of some help deciding which is the correct formula to us as well as how to create iti am looking to highlight duplicate rows of information. 

Row1 is all titles:  name (a1), address (b2), phone (C3), some of the 70 sheets I have 300 rows.   

I

... Read More
53 Views
2 Replies

Hi Victoria,

 

You may try conditional formatting

Duplicate.JPG

Read More