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 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
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 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
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
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
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
Free Tool: Port Scanner
LVL 9
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

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
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
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
I have the Excel file below with 3 columns.
On one row I have a pair of q, k corresponding to a certain x.
In the sheet are different values for x.
I need to find what pairs of q, k are common for all values of x.
How do you do that fast and easy?
Pairs.xlsx
0
I would like to perform computations on minutes elapsed
example:  50:00 = 50 minutes 00 seconds
minus        32:00 = 32 minutes 00 seconds
equals        18:00 = 18 minutes 00 seconds.
0
I'm using Excel 2016 Pivot Table and I need to show the difference from previous year of an average of three months. Example would be using averages of March, April, and May of 2016 Sales showing a difference from the averages of March, April, and May of 2017 Sales.
0
Get HTML5 Certified
LVL 9
Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

So I want to use an Excel table to show a balance. People are allocated a certain number of hours holiday (cell I1). They add to that tally by doing overtime (column C) and reduce it by taking holidays/time-in-lieu. I can do this no problem with an ordinary formula but I want to do it in a table to make this as dynamic as possible. Alas, what I *should* be getting is 213 but my table is giving me 190. See attached file. Any suggestions? Thanks as always
EE_table_dynamic-formula.xlsx
0
I have attached a chart which shows data entered on non-consecutive days. Excel is showing me all the days in that sequence. How can I get  Excel just to show the data on the dates entries were made.. thanks as always :-)
Control_chart_data_audit_EE_only_sh.xlsx
0
I would like to get another sheet's cell value referencing active sheet's cell value
0
Hi,  I am trying to create a formula based on a tiered commission.

the commission is anything up to 10, 000 you get 12% and once you hit 10,000 anything over that is 25%.

The formula works on anything up to 10,000 but once try to type past I just get #name

=IF(B17<=G5,(B17*0.12),IF(AND,$B$17>G6,(B17*0.25)))

Please not G5 = 10,000 on my spreadsheet, G6 = 10,001 Also B17 is the where the main number is

thanks for any help you can offer.

Kind Regards,
0
I have the following function in a google spreadsheet and need to adapt it so that it does not show duplicate values.
=IMPORTRANGE("tracker!A3:A1000")

Open in new window

0
Hello,
i have an excel spreadsheet 2013:


Here are my conditions:
Cells: A1, B1, C1 ,D1 and E1

if A1 <> '' , clear B1, C1, D1 and E1
if B1 <> '' , clear A1, C1, D1 and E1
if C1 <> '' , clear A1, B1, D1 and E1
if D1 <> '' , clear A1, B1, C1 and E1
if E1 <> '' , clear A1, B1, C1 and D1


how to write formula to do the above task.

Thanks
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.