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

A student is giving the test in 12 rounds and every round has 4 subjects. I want to make sheet where I can show student result of all rounds on choose subject. For example, if I choose English subject then the formula should show a result of every round which he was taken .
Sample is attached
Book1-V2.xlsx
0
Free Tool: SSL Checker
LVL 9
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Hi,

I have attached a spreadsheet with some code and functions, but I want to know if I can achieve the following.

Spreadsheet has 7 tabs.  The tabs I am interested in is tabs 2 - 7

Each tab has one row of data.  Now if you go to tab 2 - Risks, the go to column J and in the dropbox click Action.  You will now see that the row has gone.

Go to the Action tab, and you will see that the row appears here, and that is correct.  I column B you will see an R.  As it was moved from the Risk tab, it was enetered as a R for Risk.  But as it now resideds in Action, I want the cell automatically update to an A.

This movement and changes will only aplly for rows that move between Tabs 2-4,  Risk, Action, Isse and Dependancy.

Please let me know if any more info is needed.
Sample-Project-Raid-Log-0.1a.xlsm
0
I am attaching a file that has two Start entries (C4 and C31). This is just a sample set so this would be repeated for thousands of rows. I want to know how to do two things: how do I count the number of entries between each Start e.g. it should be 26 - but to do that for all the rows in the data set. Secondly how do I show the time elapsed (in hours) between each occurrence of start. (cell T7) - again for each occurrence  of this start...basically the idea is that a pump starts...does its thing. Stops and then starts again...
EE--calculating-rows-between-entrie.xlsx
0
I have an Excel 2013 spreadsheet with over 3,000 entries.  The spreadsheet has customer numbers and Company Names (see attached sample) - my original spreadsheet has many more columns but I tried to simplify for explanation purposes. On the sample I have attached there are five customer numbers (3, 4, 5, 6, 7) and only two company names.  I am trying to find a quick way to determine what customer numbers are listed with both companies.  So in my example, customer #4, #5 and #7 are the customers that are associated with both companies.  That is what I am trying to extract.  I don't need to know the customer numbers that are only associated with one company. I need to pull only the customer numbers that are listed for both companies.  Any help is appreciated.  Thank you.
0
How do i write a nested if statement for the following?

If greater than  14 but less than 16 return £10000
BUT If greater than  16 but less than 18 return £20000
AND if greater than 18 return £30000

I tried =IF(AND(C9>=14,C9<16),10000,0)
Which returns £10000 however when i try nest it, everything just returns #VALUE?


Thank you
0
I am trying to check a cell in spreadsheet and if it is blank then make a range of cells blank.   It is importatnt that I have to do it in the cell and cannot use VBA
So If I have Cell M2 then evaluate cell named RETURN_ID_1 ="" then cell the Range of cells E2:H2 to = 1
=IF(RETURN_ID_1 = "",RANGE(E2:H2)  = 1,)

Right now what happens is I get #name in cell M2 and the range stays the same.
0
I am trying to modify some formulas that I have used in the past and understand the difference between the two.

I have a spreadsheet with the TABs  'Orders' and 'KOB1'

I want to use the two formulas (so I can compare and understand) to pull information from 'Orders' to 'KOB1' using column A on both sheets.

I want to match columns 'A' on both sheets and then pull column 'C' from 'Orders' to fill columns 'B' - using the index formula and 'C' - using the Vlookup formula on KOB1.
See attachment
test.xlsx
0
I have a number/text in a spreadsheet that I am using to reference a row for vlookup.  I want to reference a number in another row without having to do another vlookup.  Can I use a formula to add one to the row reference that is 2017-29 to make it 2017-30.  I can do it manually with the pull handle, but cannot find a formula that will work.
0
I have a Summary Excel Table in a spreadsheet that is linked to a production tracking spreadsheet that needs to update in real-time as data in entered the production tracking spreadsheet.

Description of the Summary Excel Table spreadsheet:
Sheet 1:
NOTE: Sheet1 has a number of columns that are not relevant for this specific question, but I left them in to replicate the spreadsheet I will be using to implement this solution.
Col A is a list of WO#s pulled from the production tracking spreadsheet
Column headings labelled Date1, Date2, etc. through to Date12 = date that process or rework minutes were entered into the spreadsheet. Note that the minutes associated with the date entry are always in the column to the immediate right of the date column (Col D minutes (whether process or rework minutes) are for Col C date entries, etc.).
Column headings labelled Min1, Min2, Min3, Min4, Min5, Min6, Min7 = process minutes
Columnn headings labelled RWK_Min1, RWK_Min2, etc. .through to RWK_Min6 = rework minutes

Sheet2: I would like to be able to...
Sum the number of process minutes (in one column) and rework minutes (in another column) by work order # between specific date ranges listed in Sheet2, Rows 2 and 3.

I have included a sample spreadsheet as described above, containing Sheet1 and Sheet2, with a description on Sheet2 of the solutions I am hoping for.

Lastly, can you let me know if the formulas required for this solution will be a …
0
Hello All,

The strangest thing started happening this morning at our office. We can open spreadsheets just fine. We can be working in excel just fine, but then we will go to open a spreadsheet one minute later and it will hang at 100%. At that point, any spreadsheet we try to open freezes at 100%. After a few minutes, it will open and work fine. However, if we exit out of the 100% loaded window, it leaves the temp file on the desktop. When we go to open the spreadsheet again, it says it is locked by the user. It will eventually go away after a popup comes up saying "could not quit excel."

Things to note:

This is happening on all LOCAL spreadsheets. These are not networked, they are saved right to the desktop of a non-AD computer. We are running office 16 on windows 10 and windows 7 computers. This is the click to run version of  office. All updates have been done to office and the computers themselves.

Things we have tried:

Complete un-install and reinstall of office 365 (registry, temp files, even microsoft's tool to remove office)

Deleting all printers

Setting Microsoft XPS printer as default

Disabling all add-ons (we dont run any, but we checked anyway)

OffCat is showing no errors.

Any ideas?
2.PNG
1.PNG
0
On Demand Webinar: Networking for the Cloud Era
LVL 9
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

I created a spreadsheet and would like to add color but do not know how to ask the right question in Google.  Here is what I would like to happen:

I have a formula in a cell: =IF(ISBLANK(C4),"",DATEDIF(C4,TODAY(),"d"))

I would like to add color as such:  Green if it is under the maximum days allowed (in this case it would be 365) and Red if it is over the maximum days allowed while keeping the formula already in place intact.  Any assistance would be greatly appreciated.  Thank you.

Allen
0
I have a calculated value in cell E2,

In cell F2, I want to do the following:
1. display the same color displayed in E2
2. if value displaying in E2 < 20, display ADVISORY in F2
3. if value displaying in E2 > 19, display RFC in F2
4. if value displaying in E2 > 39, display ESCALATE in F2

Thank you for your help!
JohnD
test.xlsm
0
I'm getting a "Too Few Arguments" error with the following formula:

IF(ISBLANK(C2), DATEDIF(B2,TODAY()), DATEDIF(C2,B2),"d")

Basically, B2 will always have a date in it, C2 will not.  I want to calculate the number of days and use TODAY if C2 is blank, otherwise take the two dates and calculate the difference.

But what's wrong here?
0
HI Guys

Hope you can help

This may be an easy answer but Im racking my head over it for some reason

I have the following formula that re-assembles a date in a spreadsheet I have:-

=IF(TRIM(A2)="","",IF(RIGHT(B2,1)="M",LEFT(B2,9),TEXT(B2,"dd"&"/"&"mm"&"/"&"yyyy")))

What I need though is an OR statement, after the ..IF(RIGHT(B2,1)="M"... so that if RIGHT(B2,1)="M" OR RIGHT(B2,1)="0", then LEFT (B2,9) etc.

DO I need to change this to an array? Or is there a way I can insert this OR statement within this formula?

As always, your help is much appreciated

J
0
I am using the following formula and I need help making an adjustment to it.

I need to do a calculation within this formula. I need to look at two columns.

=SUMPRODUCT(--('SSG 2017 Orders'!$C$5:$C$471=K4),SUBTOTAL(9,OFFSET('SSG 2017 Orders'!$AO$5:$AO$471,ROW('SSG 2017 Orders'!$AO$5:$AO$471)-MIN(ROW('SSG 2017 Orders'!$AO$5:$AO$471)),0,1)))

the above formula with the addition of 'SSG 2017 Orders'!$AC$5:$AC$471

Example:

=SUMPRODUCT(--('SSG 2017 Orders'!$C$5:$C$471=K4),SUBTOTAL(9,OFFSET('SSG 2017 Orders'!$AO$5:$AO$471
     +  'SSG 2017 Orders'!$AC$5:$AC$471    ,
ROW('SSG 2017 Orders'!$AO$5:$AO$471
     +   'SSG 2017 Orders'!$AC$5:$AC$471    
)-MIN(ROW('SSG 2017 Orders'!$AO$5:$AO$471
     +   'SSG 2017 Orders'!$AC$5:$AC$471
)),0,1)))
0
I need to look at several cells and if they are greater than zero do a count and then multiple by another cell.

Something like this:

=COUNTIF(L10:L21>0)  ---  count the cells L10 thru L21 if they are > 0
and then
(L10:L21)*(L4))  ----  number of cell >0  multiple times L4
0
Good morning Team,

I would like to thank you for your good work.

I need to know what does OrignalTable & OrignalKey mean.
UpdatedTable& Key mean when we are comparing two spread sheets.

I have attached Macro spreadsheet for reference. Once again. Thank you for your goodwork.

Const ksOriginal = "OriginalTable"
Const ksOriginalKey = "OriginalKey"

Also Const ksUpdated = "UpdatedTable"
Const ksUpdatedKey = "UpdatedKey"
Compare-Two-Worksheets-and-Paste-di.xlsm
0
Is there a system way of adding a line after each group of cost center codes and totalling across the spreadsheet
See attached spreadsheet
The spreadsheet is compiled from data in a database and could alter each run
Maintenance-Costs-Summery-V1.xlsx
0
Hi

A user keeps being confronted to Excel (365) spreadsheets that open up in protected mode

I initially disabled all the protected mode options but apparently that hasn't prevented Excel from pursuing insistently in the same manner

any ideas ?

thanks
yann
0
Announcing the Most Valuable Experts of 2016
LVL 6
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Hello,

Is there a way to get an Excel function, such as MIN(), to disregard any cells showing #VALUE! and continue/complete its action?

For example, each MIN() function in the following two screenshots displays the correct result.

2017-07-05a_EE.png

2017-07-05b_EE.png
However, if #VALUE! resides anywhere in the range being evaluated, the formulas also return #VALUE!

2017-07-05c_EE.png
Is there a way for the function to disregard any #VALUE! terms and continue evaluating the remaining numeric values (ie so the column B function would again display 5 but the column D function would now display 6)?

Thanks
0
Hello!

I have been working on creating a database that creates bid submittals and thought I was basically done but now am realizing that the link between Table1 on the first page and the worksheets that are being created is not working. (Database is attached for reference)

When the Add Work Items macro is run, a new worksheet is created based off of the template worksheet. As the user fills in the worksheet, Table1 is populated. This works great for the first work item that is added, all of the links work perfectly. However, once more than one work item is created, Table1 only connects to the most recently created work item.. which is not what is supposed to happen. Each row of Table1 should be connected to its own worksheet. So really my question is, is there something I can add or delete from the macro to make sure that each row stays linked to the specific worksheet?

To try and explain myself better, looking at the attached database there are two work items created. Row 2 in table 1, which is for work item 2, is completely correct. All of the cells are linked properly to worksheet 2, for example the description of the work item is connected to A6 in spreadsheet 2, and all of the costs are connected to the proper cells in the spreadsheet 2. This is great!

However, the first row for work item 1 becomes completely incorrect once a new work item is added. Row 1 begins to connect to spreadsheet 2..which is wrong.. it should be connected to spreadsheet 1 still, and …
0
I simply want to sum the totals next to 2 different names in a range. Can't seem to put the formula together, which I believe would be a SUMIF function. No arrays, please, because my list can get longer or shorter in any given month. Please see attached example. Thanks!
0
Hi Experts

With reference to the attached spreadsheet, we input where candidates are spreading their time for each day, spilt by AM and PM, for example  number of hours  in college, number of hours in work placement, etc.

 I need a formula that allows me to calculate total number of hours spend on each activity per week.  That is total number of hours spent in college from Monday to Friday, total spent in work placement etc.

As per AL & AM which are currently done manually.

I have tried SUMIF but that seems more suited to columns, and cant get the IF to work at all.

Would like to do this in a formula in possible rather than VBA.

Andy
WorkbookMF.xlsx
0
Hi,
I have been doing a calculus that takes many cells in excel and I'm looking to use a formula instead.
What I have so far is, I take the amount I spend (let's say on gas) a year.
So for the first year I have 2500.
Then the second year I add 5% price hikes I get 2625.
So the cumulative for the 2 year is 5125 (2500+2625)
Same thing for the 3rd year = 2756,25 /cumulative 7881,25...
And so on up until 10 where I get 3878,32$ /cumulative 31444,73$
It takes 10 cells to add all this
cummulative-formula.jpg
I know that the formula for inflation on a fix amount is : Future Value = PV * (1 + i)^n  
PV = Present value but that doesn't count all amount spent on the 2nd year up to the 10th year...

What would be the formula for that?
tX!
0
I have column headings that look like the following:
◄───── Months Ending ─────►
◄─────Quarters Ending──────►
◄──── MONTHS ENDING ────►
◄─────Quarters Ending──────►
Located in cells C6, C7, C8 and C9, respectively)
Based on an X being in B6, B7, B8 or B9
So the current formula, (omitting B9 and C9)
=IF(B6="X","◄───────── "&C6&" ─────────►",IF(B7="X","◄─────"&C7&"─────────►",IF(B8="X","◄─────"&C8&"─────────►","SEE SETUP")))
Given the limitation to 3 nested IF statements, how do if get the 4th option, an X in cell B9 into the equation?
(Small file attached)
EE-Beginning-and-Ending-Date.xlsx
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.