Home

Formulas and Functions

890 Conversations

Latest Activity

Custom List Message Item

I am unable to find how to get information pasted in cells after 5 minutes from Another worksheet.

I have a data sheet which updates information every 5 minutes and i have another worksheet that calculates the data. Now i want the data refreshed every 5 m

... Read More
29 Views
1 Reply

Anil,

 

The best technique is to copy the cells of these data as links to worksheet2 so that they change by changing the original data.

 

Copy the data, select the locati

... Read More

Hi there,

 

I am trying to change the formatting (color) of a number of cells in an array (4 cells in a row) given a value that I am going to input in 1 of those cells. This input will be compared to an array of values already input. So depending on the

... Read More
29 Views
0 Reply

Hi, I am trying to set up a meal planner on excel.  I have created 4 worksheets planner, shopping list, recipe, smoothie.   I have drop down menus on my planner for meals, so on a Monday for breakfast I have a drop down menu and click smoothie which links

... Read More
30 Views
0 Reply

I am trying to establish a connection between an Excel file to an Excel file located on a SharePoint site in a folder. I select the "Get Data From Web option":

get data.JPG

I enter the SharePoint site URL on which the file resides and am given the following error:

Error.JPG

I

... Read More
30 Views
0 Reply
 
I have 4 comparison with 4 unknowns.
is there a function in Excel that I can use for this?
Read More
25 Views
1 Reply

You may visit Using IF with AND, OR and NOT functions for more informations.

Read More

When I try to import an excel file, I get the below error,

"The wizard is unable to access information in the file '<file_path>.xls. 
Please check that the file exists and is in the correct format." 

 

When I open that file in excel and just save without a

... Read More
45 Views
0 Reply

Hi all, 

 

I'm a bit of a novice when it comes to excel formulas, so any assistance I can get on the below would be amazing!

 

I need a formula that does the following: 

 

  • On 1 sheet I have a table of numbers
    • The column headings contain a 3-digit job nu
... Read More
86 Views
4 Replies

I am not good at Excel.  For this question, I can think of array formula solution only.

 

For Total Cost of Sales

=INDEX('Sheet1'!$B$6:$D$21, 2116, MATCH(TEXT('Sheet2'!$A
... Read More

Let me apologize in advance if i have a hard time explaining this. I'm not even sure if excel can do this.  I have a spreadsheet where i want to be able to sum up Overtime Hours and Comp hours in the same row.  What i would like to do isL: for overtime pu

... Read More
96 Views
5 Replies

in 1stQ

comp

=SUMPRODUCT(VALUE(LEFT($H6:$CS6,LEN($H6:$CS6)-1)) * (RIGHT($H6:$CS6,1)="c"))

overtime

=SUMPRODUCT(VALUE(LEFT($H6:$CS6,LEN($H6:$CS6)-1)) * (RIGHT($H6:$CS6,1)=
... Read More

Does anyone know how to adjust the formula to see the previous year's calendar view for each individual employee?  When we rolled into January, I can no longer pull up th

... Read More

Me again .. (almost think I'm new to excel ! )

Here's what I want to do:

if K7 is less than 3 enter 112.5, if K7 is between 3-9 enter 150, if K7 is greater than 10 enter 187.50


here's the formula I have but I've got something wrong ..

=IF(K7,">3",112.5)*O

... Read More
41 Views
2 Replies

Thanks.. the beginner in me figured it out on my own

 

Kimberly,

 

This is for you:

=IF(K7<3,112.5,IF(AND(K7>=3,K7<=9),150,IF(K7>10,187.5,"")))

 

If you want the last condition to be greater than or equal 10, take this:

=IF(K
... Read More

I am trying to create a IF statement that will work for the following situation:

 

I currently have the sheet set up to calculate dates based upon previous data input.  Column G will calculate a date of column A + 45 days.  However, if column H has a date

... Read More
34 Views
1 Reply
=IF(A7="","",IF(OR(ISBLANK(H7)), TEXT(A7+45,"dd-mmm-yy"),"see Due Date after resubmission"))

Add a if formula to wrap your column G's formula is a simple why way to fix i

... Read More

Hi,


I have what is probably a basic question but I'm having a moment and can't figure it out!

 

I want to do a simple sum formula but exclude one column only if it contains a percentage.  Sometimes it will contain a number, which I want the formula to the

... Read More
77 Views
6 Replies

This formula is fairly good:

=SUMIF(A1:A10,">=1",A1:A10)

But it ignores all percentage values except 100% and above.

 

SUMIF.png

Read More

Hi.

 

I need a formula that helps me list supplier name situated in column C under column F against codes listed in column A. I am attaching a "Test" excel sheet of what the final result needs to look like (result marked in orange).

 

Example:

 

Supplier

... Read More
41 Views
1 Reply

Hi Christian,

 

I have a solution for you.

In cell G2 add this formula and drag it down:   =IF(LEFT($C2,8)="Supplier",C2,G1)

In cell H2 add this formula and drag it down:

... Read More

Hello,

 

I have a ton of data about our production over the last 2 years. The data is contains all the produciton jobs we ran over the past two years. For every job it shows its production route. My goal is to find out what the main production routes were

... Read More
15 Views
0 Reply

Hello, I have a workbook with 4 different worksheets (4 different tables). I would like to be able to create an extra worksheets that incorporates all 4 tables into one and that is reactive to changes in either of the 4 initial tables.

 

Thank you!

Read More
60 Views
3 Replies

Hi Jean,

 

Although I agree with Jan that the best & easiest solution would be to create one combined table and work with this....there are a couple of ways to combine yo

... Read More
Why not copy all four tables to the one sheet and then get rid of the four original sheets? Add a column to the new sheet which contains the name of the tabs the data ori... Read More
Hi. I'm a complete beginner to Excel and need some help please.
I'm doing a spreadsheet comparing prices from different companies for hundreds of products so I'll have about 10 columns and up to 1000 rows. I want the cheapest price in each row to be highli... Read More
54 Views
3 Replies

I am sorry, Kim.  I overlooked "be mainly using Excel android app".  I don't have Excel android app. With a search on web, I found this question too.  As of July 2017, co

... Read More
  1. select the first cell in the first row, e.g. Cell A1
  2. Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format
  3. type the following formu
... Read More

Dear Kim,

 

Do the following steps as below screenshot:

1- Highlight the entire first column then go to Home >> Styles >> Conditional Formatting >> Top/Bottom Rules >> Bo

... Read More

I have used Excel for many years, but have exhausted my knowledge on this one.

 

The worksheet that I am trying to fix is labeled "100.01". On "100.01" the first 55 instances are pulled from "General Ledger" but not the 56th.

 

The same error occurs on ev

... Read More
29 Views
1 Reply

Tamara,

 

you have over complicated your model.

Make use of Excel tables, Pivot tables and Power Query.

Input would be "Chart of Accounts" and "General Ledger" (without t

... Read More

Here's the Scenarios, 

 

 

If any row in the first column of PivotTable1 equals any row in the first column of PivotTable2, then perform this formula (PivoTable1 Second column/ PivotTable2 Second column) for each row 

 

I need to compare names between eac

... Read More
30 Views
1 Reply

Bianca,

 

please provide a sample workbook.

 

I have a 15 digit number. All the numbers have to remain the same except for the 10th digit and the last digit. The 10th digit needs to go up 1 and the last digit needs to go up 7. If the last digit starts at a 7 and adding 7 makes it 14, the 1 in the 14

... Read More
42 Views
2 Replies

How about the 10th digit & the 9th digit?

 

Assume your 15 digit number on Cell A1.

 

If they act like 14th & 15th digit

On Cell A2, type

=LEFT(A1,9) & RIGHT(VALUE(MID(A1
... Read More
Best Response confirmed by Erin McCoy (New Contributor)

Hi,

 

In column A of workbook 1 I have a number of customer ref codes. I want to do a v-lookup of these codes into workbook 2 to return the orders associated with these codes (located in column D). I want these orders returned to column P of workbook 1.

 

... Read More
73 Views
4 Replies
Hi adobinson95, This formula will get all the matches for each individual code: =TEXTJOIN(", ",TRUE,IF($A2='[External Workbook.xlsx]Sheet2'!$A$2:$A$25,'[External Workbook... Read More

Hello,

If I had time to take a course in Excel, I would. I just need to know the formula for a Balance Column.

All of the formulas I have tried work for one row, but I cannot get them to work for the following rows, without typing them in for each cell. C

... Read More
35 Views
1 Reply

Hi Iora

 

I've mocked up your sheet and done the formula and it appears to work fine for me. Very similar to your formula, but mine is =SUM(F2, G2, H1) and then auto fill

... Read More

I have an attendance tracker that has a YTD summary sheet and a sheet for each quarter. I have used a COUNTIF function on each of the quarter sheets   =COUNTIF($E6:$CQ6, ">0") that totals the number of cells that have any entry in them.

I want to have a c

... Read More
22 Views
1 Reply
Hi Michael

Assuming your data is layed out the same in every sheet, you could insert this formula into your Summary sheet to sum the results of the other sheets:

=SUM(Sheet... Read More

Here is the formula I'm working on

F1=SUMIFS(C:C,A:A,E1,B:B,'sheet1:sheet2'!A1)

 

  A B C D E F
1 Jan Mary 100   Jan  90
2 Jun Susan 300   Feb  
3 Jan Peter 50   Mar  
4 May Richard 80   Apr  
5 Jan Evan 40   May  

sheet1!A1=Peter 

sheet2!

... Read More
100 Views
6 Replies
=SUMIFS(C:C,A:A,E1,B:B,'sheet1'!$A$1)+SUMIFS(C:C,A:A,E1,B:B,'sheet2'!$A$1)

Hi,

 

You have to put Evan and Peter on the same sheet as below screenshot, then use this formula:

=SUMPRODUCT(SUMIFS(C:C,A:A,E1,B:B,C9:C10))

SUMPRODUCT.png

Read More

Hi all,

 

I've been attempting to create a formula to help me streamline a process for what feels like forever. 

 

I want to search for text (from a list of texts) within a cell, and then if that text exists, return that text. The list of texts is approx

... Read More
54 Views
2 Replies
Thanks so much Sergei - this worked perfectly! I regret wasting hours and not posting here sooner now :) Thanks again Abi

Dear All,

 
Sheet 1 summarises Values listed in Sheet 2.
Sheet 1 has categories listed in Column A , and a formula in Column B
Sheet 2 contains a list of expenses that I want add up in Sheet 1, by using the Row numbers in Sheet 1 as a reference, rather th
... Read More
71 Views
2 Replies
Have you considered using a pivot table? Very flexible and no formulas required at all.

Hi, 

Within a spreadsheet i have created a REF TAB where I have created many lists:

  • months of the year (jan, feb, mar etc)
  • Manager name (jon, bill, fred etc)
  • Risk impacts (revenue, lost of service, technical dept etc). 
  • .....

Using "Name manager" I have

... Read More
23 Views
0 Reply