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

This is a different question, my formula in vba is
=((((q_i^b_1)/((((1/b_1)*((1-d_i)^(-1*b_1)-1))/365)*(1-b_1)))*((q_i^(1-b_1))-((q_i/((1+b_1*(((1/b_1)*((1-d_i)^(-1*b_1)-1))/12)*(t_b2-1))^(1/b_1)))^(1-b_1))))+((((q_i/((1+b_1*(((1/b_1)*((1-d_i)^(-1*b_1)-1))/12)*(t_b2-1))^(1/b_1)))^b_2)/((((((1/b_1)*((1-d_i)^(-1*b_1)-1))/365)*((q_i/((1+b_1*(((1/b_1)*((1-d_i)^(-1*b_1)-1))/12)*(t_b2-1))^(1/b_1)))/q_i)^b_1))*(1-b_2)))*(((q_i/((1+b_1*(((1/b_1)*((1-d_i)^(-1*b_1)-1))/12)*(t_b2-1))^(1/b_1)))^(1-b_2))-(((q_i/((1+b_1*(((1/b_1)*((1-d_i)^(-1*b_1)-1))/12)*(t_b2-1))^(1/b_1)))*((-LN(1-d_min)/365)/(((((1/b_1)*((1-d_i)^(-1*b_1)-1))/365)*((q_i/((1+b_1*(((1/b_1)*((1-d_i)^(-1*b_1)-1))/12)*(t_b2-1))^(1/b_1)))/q_i)^b_1)))^(1/b_2))^(1-b_2))))+((((q_i/((1+b_1*(((1/b_1)*((1-d_i)^(-1*b_1)-1))/12)*(t_b2-1))^(1/b_1)))*((-LN(1-d_min)/365)/(((((1/b_1)*((1-d_i)^(-1*b_1)-1))/365)*((q_i/((1+b_1*(((1/b_1)*((1-d_i)^(-1*b_1)-1))/12)*(t_b2-1))^(1/b_1)))/q_i)^b_1)))^(1/b_2))-q_ab)/(-LN(1-d_min)/365)))+(t_flat*q_i*30.4)

Can someone help me in solving it for
qi, b1, b2, t_b2, di, dmin, qab.


Thank you.
0
Expert Spotlight: Joe Anderson (DatabaseMX)
LVL 13
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Using Google Sheets where column D contains a list of email addresses previously in use. In comes a new email containing basically the same list of emails, but potentially with some additions which I then add to the bottom of my sheet in column D.  What I want to do now is highlight all the newly pasted emails that are not in the original list above. The first list is D2:D226 and the new list is D228:D270.  Items in D228:D270 should be highlighted if they are duplicates so that I can remove them.

The instructions on this page https://stackoverflow.com/questions/21899516/how-to-highlight-cell-if-value-duplicate-in-same-column-for-google-spreadsheet say to do the following.

Select the whole column
Click Format
Click Conditional formatting
Click Add new rule
Set Format cells if to: Custom formula is:
Set value to: =countif(A:A,A1)>1
Set the formatting style.
Ensure the range applies to your column (e.g., A1:A100).
Anything written in the A1:A100 cells will be checked, and if there is a duplicate (occurs more than once) then it'll be coloured.
For locales using comma (,) as a decimal separator, the argument separator is most likely a semi-colon (;). That is, try: =countif(A:A;A1)>1, instead.

I don't fully understand the syntax to be used or how to implement it. Assistance is greatly appreciated.
0
I'm planning to use the export to Google CSV file to get contacts out of google for manipulating and then re-importing them.

The trouble I see currently is that the photos won't be likely to re-populate.

Does anyone have a tested solution for this?

Assistance is greatly appreciated.
0
Fill Google Spreadsheet with data from Google Contacts

My Google Contacts database is potentially one of the most valuable information resources that I have and I would like to be able to sort and filter it more efficiently.  The built-in options at contacts.google.com just don't do enough and exporting and importing is very old-school.

I need to be able to sort and filter them using a Google Spreadsheet instead of being relegated to the minimal options provided within the context of the Google Contacts page itself.  

One option might be to use the Google scripting area under the Tools menu item within the spreadsheet. Assistance is greatly appreciated.

I would greatly appreciate any assistance in resolving this challenge and I have begun a sheet below to give us a point of reference as I try to find a solution to implement here.

https://docs.google.com/spreadsheets/d/14OH97o7dSVa5eC4WXjwHKdzmsJEinQLrVldaqOF-MwE/edit#gid=1636004216
0
I need to calculate CAGr by our Fiscal Year (which starts in November) for balances for several different accounts based on the open (beginning) date of the account.

For example account A opened November 25, 2015, Account B opened June 2, 2009, Account C opened February 15, 2019. The Fiscal year begins in November of each year

Not sure if this is relevant, but the balances are already cumulative; i.e. the balance for the most current month is the sum total of all previous months/years.

If an account doesn't have a full year's history, I'd like it to be able to pro-rate CAGr based on the month we're currently in.

I have a table already set up for fiscal years, but it's in a different table than my accounts and balances and open dates. The tables are linked, so not sure if that even makes a difference.  

Ultimately, I need to be able to select an account based on a slicer or some other filter (I already have set up); it should then calculate the CAGr in a card or table visual based on the selected account.  Behind the scenes, it should also know the beginning dates of each account (which is used to calculate CAGr).    

Unfortunately, I can't send a sample file due to the confidentiality of the data, but hoping this is fairly intuitive the way I've explained it here. Thanks in advance for your assistance!
0
Office  365  says I have too many working assets when I upload an excel spreadsheet with
60 columns (basic math formulas).
And 20,0000 rows.
I am looking for maybe an alternative such as an excel style tool where it works with such big spreadsheets and must have the ability to collaborate with 5 people at once.
0
In radSpreadSheet how can I set a cell value by its name? meaning excel cell "B1" for example
0
I'm trying to read through our phone system log and increment a number in a Google sheet to track  how many calls we have for a particular hunt group "BState=Ringing" and then decrement the number when the call is finished "Deleted".  I would like to read a line from the log, check to see if the call is for "HuntGrp1", if "BState=Ringing" then copy unique identifier "16.12345.1" to an array, increment a cell in a google sheet.  Then I will have to check each matching "HuntGrp1" log line to see if that unique identifier is in the arrary and delete it from the array and decrement the cell in the google sheet when log line containing "Deleted" is encountered.  I have tried multiple ways to add elements to array/collection with  inconsistent results.  Here is what I have and some sudo code.

$RegexHG = "HuntGrp1\(304\)"
$UICollection = {$callid}.Invoke() #Collection for Unique Identifiers

Get-content "C:\Temp\TestData.txt" -Wait | where {$_ -match $RegexHG}| 
foreach {   
   If ($_ -match "BState=Ringing") {
    #increment counter Google Sheets
    #add unique identifier to arrary
   }
   Else {
   	#If unique identifer in array
   		If ($_ -match "Deleted") {
   			#Remove unique identifer from arrary
   			#decrement counter Google Sheets
   		}
   }
}

Open in new window



Sample Data
08:00:56 1299975656mS CMTARGET:     c0a800f40000a7bc 16.12345.1 11380 SIPTrunk Endpoint: LOOKUP ICR: DDI=1234567890 CGPN=0987654321@1.1.1.1  Matched Time profile: Name1 (Destination 

Open in new window

0
I have extracted data from our application into excel and one of the cells contains data laid out like this;

$250,000.00 - Building

$  35,000.00 - General Contents

Is there a way to extract the numbers and then add them to get a grand total in another cell?
0
I am using 2 spreadsheets.
1 is DataBasexls
the other is
PartLookup.xls

I want the PartLook up database to react this way:
In field A2, if I enter a part number, perhaps from a dropdown, and select it.
then I want field B2 and B3 to show the description and quantity.
those fields are also in DataBase.xls file.

How can I start this..
0
Bootstrap 4: Exploring New Features
LVL 13
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Hi,

We share a Google Sheets spreadsheet with our client.

Is there a way of finding out when the client viewed our shared spreadsheet and for how long?

Thanks,
Robbie
0
Hello all, and Happy Friday!!  
I've been trying to build a useful spreadsheet for a client in Google Sheets that requires three functions I am having difficulty with, and they are so linked together, I am afraid to post them as separate questions.  Here goes:
1) I would like to automatically export data from only one tab (Tab D) of a multi-tabbed daily spreadsheet into one separate new monthly summary spreadsheet. (Outcome: all data from tab 'D' from all (m-f) daily reports appears in a new single summary of that tab for the entire month. So my daily reports have tabs a, b, c, and D, but we get to see a whole months worth of 'tab D' in its own separate monthly report.)
2) In a separate tab in this new monthly summary report, rather than listing the litany of measurements we've made that month and have a clunky, crowded spreadsheet,   I would like to include a drop-down menu that indicates different criteria, and when that items is selected, that function is executed.  I've attached a small sample of what I mean. And lastly,
3) I would like a chart accompanying that selection to appear when selected, if possible.  So in the example I attached, when I click on 'Brand New Member - Did Not Work Out' my desired outcome is that the number of members who signed up for the gym but did not work out would be displayed (that data lives on another tab in this same report) AND that the chart/visual for that data ALSO appears when that item is selected from that drop-down menu...

0
Hello Experts Exchange,

I have a client that recently added an address lookup feature in a workbook to check for an address across multiple spreadsheets in the same workbook. The address information in the workbook goes from 2007-present, but from my observation it is only returning data for certain years.
The error is =IFERROR(INDEX($A$6:$C$3000,$J6,COLUMNS($J$5:J5)),"").

Thanks in advance for any assistance!
eesnip.PNG
0
Hi,

I have a Google Sheets spreadsheet shared with a few other people who have edit access.

I would like to hide one of the columns in this spreadsheet as the column is not needed but want to have the ability to unhide if it is ever needed.

1. Is there a way of hiding a column from all people who have access to the Google Sheet spreadsheet but where I am the only one who can unhide the column?
2. Is there a way of entering a column into a shared Google Sheets spreadsheet where I would be the only one who could see the column data?

Thanks,
Robbie
0
There is a hidden column L in the source file ( keno results) which is upsetting the formula when  I delete the column L  in the source.

I deleted the column L in the source file and now I am able to do a exact copy and paste into the manually entered excel file(destination file) and it pasted correctly into the cells c:v

but in doing so I lost the formula because the monitored numbers count did not get updated.

How do I save the formula, while at the same delete the hidden column L in the source file ?

I also type 20 numbers for the 31st March (just to test out)  in the source file and to see the if results get updated in the destination file , but it did not get updated. I highlighted row 122 and pulled the fill handle down to row 123 to populate the formula and the source file was requested and I clicked on the source file and after that the destination file did not get updated.

keno-reuslts.xlsx source excel file
manual-entered-CondFormatQ29141105.xlsm destination excel file
0
In the attach example, i have two sheets, sheet one has total amounts for each Roll number and sheet two has detail amount for each roll number. I would like to highlight those amounts  in sheet two which will be equal to the total amount in sheet 1 for each roll number.
For example in the attach sheet, For roll number 225 in the sheet one, the amount is 40, so it has highlighted  5 10 and 25 in sheet 2 because if I add it, total will be 40. It did not highlighted 18. Is there anyway if we can do this through VBA.
thans
example-2.xlsx
0
I have one of my users that is having an issue in Excel.  It seems that any excel file he has, complex or simple calculations has this same issue.  If he leaves the file open, and does other things on his pc, or steps a way for a while, Excel is very slow to respond when he clicks a cell on the spreadsheet.  He has several spreadsheets he updates every day.  He is so used to this issue, that he knows to click on a cell and go work on other things for a minute or so, then comes back and works in spreadsheet.  It acts like excel is hung, but always comes  back and stats working.  You just have to wait.  Everything else on his pc responds great.  He said many other co-workers have told him they have the same issue.  We run Microsoft Office 365 ProPlus, and we do use cloud storage such as Microsoft's OneDrive.  I think most of his files are stored on SharePoint, and this also has cloud storage back to Microsoft.  Almost seems like Excel has its own hibernation mode.
0
We have Office 365 and have has issues for over a month with Excel spreadsheets becoming corrupt. We've had issues with various spreadsheets, some become corrupt after a few days. It's truly been hit or miss, sometimes we have issues 3-4 times a day, then we go 1-2 days without an issue. I scanned the file server for viruses and nothing. I've called Godaddy who we purchased Office 365, they're not much help. I called Microsoft and they told me to call Godaddy since I purchased our licensing through them.

I've gone through the below link and performed the suggested actions to remedy the issue.
https://answers.microsoft.com/en-us/office/forum/office_2016-excel/file-is-corrupt-and-cannot-be-opened-in-excel-2016/3e378431-83c6-474e-a852-eaea3bb802a8/?fbclid=IwAR2nEOy3kdUJLOKb3df5ZdhwWALN08wu70q--zOUNujEc5AHDU_U2mpAjtk

I will try and clarify, its not one or two people. We have 5 users that modify documents and send them out to clients. On another hand our HR department makes changes to Time Card files within Excel where nobody else can access. There is not one specific user or file to pin point the issue. Our head of HR doesn't access the files from the staffing side that are experiencing these issues, and vice versa.

So the pop-up users receive is attached, only thing the changes is the file name. Most of the time  they click YES and it opens but the formatting is lost. This is a big issue when sending the documents to clients and they receive the pop-up, they proceed …
0
Dear Experts,

I'm having trouble to resize or delete the combo box in Excel on Mac.

Can you please help me ?

Thanks,
Combo-Box-Practive.xlsx
0
Learn SQL Server Core 2016
LVL 13
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Hello, I would like to get some values at openoffice calc importing from google spreadsheet. I put two pictures files as reference: The one is google spread sheet picture file - Googless.jpg, Another one is Oracle Openoffice calc picture file - Openof.jpg. First of all, I add some values in each columns(PID, category, product, test c, price from the left to the right). And I have made open office calc file at the desktop / my local PC. Everytime I add PID number at my local open office cal file, I would like to automatically grab the values at category, product and price at google spreadsheet and put the values at the row where PID number is located at Open Office on my local PC. How can I get it? Sincerely, portal
googless.jpg
openof.jpg
0
Hi, I am wanting to find out if it possible to set up hyperlinks in a spreadsheet that will still be correct  and unbroken (I think they may be called dynamic links) if I move the document to a different drive or computer. I develop the spreadsheets on my computer and then once I have finished it I save it on their computer and it would save me so much time if I did not have to redo all the hyperlinks once it is on their computer.  Thanks in advance for your help. Regards Dot
0
need a hand with an excel formula.
Here is very simplified view of my columns

Ord#	Line#	Backorder
100	1	0
100	2	1
100	3	0
100	4	1
101	1	0
102	1	1
103	1	0
103	2	1
104	1	0
104	2	0
105	1	1
105	2	0
105	3	1

Open in new window


the third column acts like a Boolean. 1= backorder, 0 = not backorder
My goal is to report, how many orders are affected by back orders?

in this example 3 orders contain backorders

How can I accomplish this total in Excel ?
0
Hello,

I have two columns of computer names - please see attached.  

I want to list computers which are in column 1 but not in column 2 and put the result in column 3

Please advise.  

Thanks.
test.xlsx
0
I have customers using Office 365 that are experiencing issues with the formatting of Excel.  One user creates the spreadsheets for distribution, some employees can view and print them as they were created but some must edit the document or it displays and prints with the top of the next page on the current one.  All of their settings are the same, views, margins, etc.  We have even looked at printing to the printer of the creator but the same issue is there.  What setting(s) are we missing that the users might have set differently?
0
I have a user that saves a networked excel spreadsheet using excel 2013. They save it as a 97-2003 workbook. At that point it opens just fine on the 2013 excel but users that have 2007 it only shows a blank page.
I have done the following:
  1. unregistered excel with excel /unregserver
  2. i have unchecked DDE
  3. I have repaired office
  4. I have removed the XLStart folder and let it recreate it
nothing seems to work. Oh and the 2007 system is running the compatability pack and is able to open other excel spreadsheets just fine.
Thanks for your help
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.