Go Premium for a chance to win a PS4. Enter to Win

x

Spreadsheets

A spreadsheet is an interactive computer application program for organization, analysis and storage of data in tabular form. Spreadsheets developed as computerized simulations of paper accounting worksheets. The program operates on data represented as cells of an array, organized in rows and columns. Each cell of the array may contain either numeric or text data, or the results of formulas that automatically calculate and display a value based on the contents of other cells.

Share tech news, updates, or what's on your mind.

Sign up to Post

I got a series of questions and thank for some experts I managed to solve all of them.

But now I got a new requirement to calculate the "working days" not only limited to a particular month, but this time to allow cross month calculation.

Please check the sheet "Calendar (Expected)" for what I try to achieve.

Current:

Untitled.png
Expected:

Untitled1.png
Thank you.
Costs.xlsx
0
Vote for the Most Valuable Expert
LVL 7
Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

how can I lookup with two values in Excel. Please read sheet 2 from sheet 1 in the attached example.
grid-map-analysis.xlsx
0
Hi all,

From last few hrs i have been trying to make a Pivot table, Column "C" "Task" based Person based Count.

Wanted the work done by
How many Voice, Editing, Camera, Anchor work did "Micheal" do

If anyone can help me would be great
attached sample file
Thanks in advance
Sample.xlsx
0
I am ranking (column AH)  the points for a group of members based on if they meet all percentages greater than or equal 100.  My problem is when the point total (column AG) for a non-qualified member is greater than the qualified member.  I cannot figure out how to exclude the non-qualified member from the formula.  I've attached my spreadsheet and my formula is:  

=IF(NOT($G2>=100%),"Not Qualified",IF(NOT($M2>=100%),"Not Qualified",IF(NOT($S2>=100%),"Not Qualified",RANK($AG2,$AG2:$AG4)+SUMPRODUCT(--($AG$2:$AG$4=$AG2),--($D$2:$D$4>$D2)))))
Ranking_EE_111417.xlsx
0
hi
Just wanted to find out if there is any DateDif configuration that would show the number of years and days between two dates eg. the difference between 12/12/1945 and 14/11/2017 is 71 years and 28 days. Or indeed some other formula?
Thanks
0
I have a column in spreadsheet.  It has a (') in front of each number '006040001
When I use the find and replace with a space it turns only some of the numbers into something that looks like a formula that looks like this 9.9502E+17 instead of the number.
I tried formatting the column as text and I also tried formatting the column using copy, paste value only.

In both cases I still get this on some of the numbers.  If I click on it the real numbers do come back  but I need this to just be a text value as I am using it for a naming convention.  The number were generated by a program I have no control over.
.
0
I'm trying to enhance a calendar developed by some experts.

This is what I have so far. The Excel generates calendars based on value selected in A1.

SnapShot.png
But I got one issue, pls check formula of J18, the formula returns 3 for total working days, which exclude the Public Holidays and Off Days.

My problem comes in if I accidentally entered 25 Dec 17 into the worksheet: Off Days. Then the formula in J18 returns as 2, which is incorrect (because 25 Dec 17 was double-counted).

Can anyone advise what need to adjust?

Thank you.
Costs.xlsx
0
This follows on from a previous question and some validation checking.

=IF(AND(D37="Yes",D39="Town"),"(Co(z) + 0.6)/1.6)^2",IF(AND(D37="No",OR(D39="Sea",D39="Country")),"Ce(z) x qb","Ce(z) x Ce,t x qb"))

Simplified version

=IF(AND(D37="Yes",D39="Town"),"Equ3",IF(AND(D37="No",OR(D39="Sea",D39="Country")),"Equ1","Equ2"))

Based on the Eurocodes for wind design on which I am basing this spreadsheet I have an issue.  

The final answer for Equ 3 should equal Equ1 + equation 3 ( which is static = Co(z) + 0.6)/1.6)^2) or Equ 2 +  equation 3.

Not sure of how to build this one. I had thought using the cell E41 adjacent to D41 to display?
scaffoldv3.xlsx
0
I need to check the values of a number of cells and keeping getting errors. D39 is a 'list cell' to control entries

1st check
=IF(D37="No", And(D39="Sea"or"Country")"text1=iftrue","text2=iffalse")

if I then change cell D37 to 'Yes' then
=IF(D37="Yes", And(D39="Sea"or"Country")"text3=iftrue","text4=iffalse")

Thanks in advance Paul
0
All, I have a formula that kind of works but for some reason its pulling the names from the array into the first column.

=VLOOKUP($A2,Sheet2!$A$5:$K$175,MATCH(Sheet3!B$1,Sheet2!$B$4:$J$4,0))

I am just trying to match the totals under the dates in the VLOOKUP table that I made. The only problem in my first column it matches the names instead of giving me the totals, but after the first column the totals appear. I cannot share the data since its confidential.
0
New feature and membership benefit!
LVL 11
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

All, I know I have asked this before but I cannot find it in my answered questions. :( Can someone give me a weekending date based on the date of the dates in the "N" column starting at N2. Any assistance is appreciated. I need it to show the Fridays date please.
0
Hello,

I want to repeat every date in a month four times. This exercise is required every month. How can I do it easily every month without a macro. As given in the file.

Sanjay.
Month-Dates.xlsx
0
Is there an easy way to put two columns on a page that should both have the same information and figure out what value is missing that is in one column that is not in the other column. The value could be missing from either column.
0
I found a attendance result sheet where i can add daily wise attendance of each student. If i enter "A" student is considering as absent. Absent student cell is showing as RED. I want to change color of this cell ??

Can anyone guide me how to do this ??
Student-attendance-record-2-1.xlsx
0
I'm trying to enhance a calendar developed by some experts.

The calendar is working fine to populate the dates of a calendar month (based on value of A1)

SnapShot.png
But I need 2 enhancements (second one is optional):

1. I got a list of days in tab: Public Holidays. What I wish is to minus the working days calculated at column J.

For example, currently cell: J8 is calculated = 5, but since 25 Dec is a public holiday, can this value be deducted to 4?

If this even possible, can we apply a Conditional Formatting to mark that cell with black background color?

2. As you can see the date "3" is selected with green background color, but since I had selected A1 = Dec 17 and it's actually not today (3 Nov 17)'s date, can the Conditional Formatting only applied when it's actual day (today), not based on month day?

Attached is what I currently have.

Thank you.
Costs.xlsx
0
I have an excel pivot table that has subtotals
Subtotal is at the top

Label                   Count of upd_Apps   Sum of upd_Apps
upd_Apps             5                                       499
App1                      1                                        53
App2                      1                                        14
App3                      1                                        80
App4                      1                                       297
App5                      1                                         55
Label                   Count of upd_Apps1   Sum of upd_Apps1
upd_Apps_1                          3                                 565
WINAPP1                          1                                 45
WINAPP10                         1                                131
WINAPP11                          1                                389

Is there a way to hide the detail e.g. App1 or WINAPP1, and display just the subtotal lines?
If not, I will use sumif and countif functions instead of the pivot table.

Thanks
0
I have a column of cells that has data in it (in the form of sentences). In column B, I would like it to say "Added Lesson" if there is text in column A saying "Added Lesson". If there is not text that says "Added Lesson" it should say "Other".

THanks!
0
Hi,
I'm looking for a time saving formula where I don't need to enter all the ranges in for each sheet every time.
Example

=COUNTIF(AV14:AV6520,">0")

I would simply enter the following
A1    14     B1   6520

and all formulas for same row would reference the range for col AV

Many thanks

Ian
0
The Original Data Sheet in the attached spreadsheet shows x, y coordinates and the corresponding true z values.
The Interpolation Sheet shows a different set of x, y coordinates (some overlapping), and the interpolated values imported from two different algorithms - bilinear and bicubic interpolation. There is also a column called z_truth.

My goal is to determine whether bilinear or bicubic interpolation is better.
It may be that for this initial data set that one sometimes does better than the other and vice versa.

To get a sense of how the interpolated data matched against the original data, I started copying rows from the Original Data Sheet to the Interpolation Sheet placing x, y coordinates from one near the other. If the coordinates match exactly, then there is only one copy. Otherwise, there are two copies where the original x, y coordinate falls between two x,y coordinates in interpolated sheet.

Currently, I am just going one row at a time from the Original Data Sheet and copying it to one or two locations in the Interpolation Sheet. This is taking too much time. I was wondering whether this copying could be automated.

If you could also help me with some metric to identify whether bilinear or bicubic interpolation is closer to the true values, I would appreciate that.

(I am now aware that my choice of original true value coordinates may not be so great; but this is a (good?) first start.

Thanks,
Paul
Compare_2d_Interpolations.xlsx
0
Free Tool: Subnet Calculator
LVL 11
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

I have a spread sheet with data in cells (C:H) of each row.  Minimum & Maximum are in columns I, J respectively.

I want to highlight the cell that matches the Max - column J.

Matching J2 seems to work when the Max is in column C but doesn't match anything when the Max is in column D:H.

What am I doing wrong?

The formula I have is
=J2 
applies to =$C$2:$H$214

Open in new window


Data Columns C-H
Min Column I
Max Column J
Range - $C$2:$H$214
0
Hello,

Seeking assistance on a formula that sums column D per ID (column B).  If the result is more than 0 it should read 'True' in the result column (E).  Here is the catch....I also need column D to show as 'True' for all other matching IDs.  So in the attached example, you see there is a 1 in cell D5.  Since the sum for ID 65656 is greater than 0, all rows that have ID 65656 have a 'True' in column E.

Thanks!
EE-Example.xlsx
0
Using Excel 2003... I have an order number (Ex 36146) and I've setup a "key" on a spread sheet to convert a number into a letter.  So the key is:

1 = A
2 = B
3= C
.....
0 = J

I would like to have excel convert 36146 to letters and I've done that with 6 cells using offset.

Two things I would like to change:

1. Instead of having 6 cells a single cell with the order number 36146 and have excel convert to letters (CFADF)

2. Reverse the result, in that if a user enters CFADF Excel will find the order number (36146).

Please  see attached file.... is this possible?

Thank you.
Order-To-Alpha.xls
0
hello I am comparing titles in two spreadsheets
my vlookups are not quite working

see attached file

=VLOOKUP(A1,Sheet1!A1:A10,FALSE)

I am looking for approximate match
0
In column M of the attached spreadsheet, I have a formula (thanks to this forum) that identifies the last date that each work order was worked on:

=LOOKUP(2,1/(B3:L3<>0),B$1:L$1)

Open in new window


I now need to identify the earliest date that each work order was worked on in Column N of the same spreadsheet, as follows:
  • For each row in Column N in Sheet1 of spreadsheet, find the first column between Col B and Col L that has a value > 0, and then enter the date listed in the heading row of this first column into the cell of the corresponding row under Column N.

I have attached a sample spreadsheet and have populated the desired results in red in Column N, as well as shaded the corresponding 'first column' cells in orange to illustrate what I am after.

Thanks!
Andrea
EE_First_Date.xlsx
0
I have a Summary spreadsheet called "PA_Summary_KPI_07DEC2017.xlsm" that is linked to two other spreadsheets, but the linked files are behaving differently.

Description of links in  "PA_Summary_KPI_07DEC2017.xlsm" spreadsheet:
Column B, C, D and E:
  • linked to spreadsheet called "Metrics_UnitWOs_07DEC2017.xlsm"
  • links only work when linked spreadsheet is open; otherwise, no data visible in Columns B, C, D and E of Summary spreadsheet
Column F, G and H
  • linked to spreadsheet called "Metrics_CAP_ADDWOs_07DEC2017.xlsm"
  • links always work and data always visible, whether linked spreadsheet is open or closed

I would like to understand what is causing the different linking behavior between the two spreadsheets, and how I can fix it so that no linked spreadsheets need to be open when I open the Summary spreadsheet.

I have attached all 3 spreadsheets to illustrate the issue.

Thanks,
Andrea
Metrics_CAP_ADDWOs_07DEC2017.xlsm
Metrics_UnitWOs_07DEC2017.xlsm
PA_Summary_KPI_07DEC2017_EE.xlsm
0

Spreadsheets

A spreadsheet is an interactive computer application program for organization, analysis and storage of data in tabular form. Spreadsheets developed as computerized simulations of paper accounting worksheets. The program operates on data represented as cells of an array, organized in rows and columns. Each cell of the array may contain either numeric or text data, or the results of formulas that automatically calculate and display a value based on the contents of other cells.