Excel Help and Problems

[JTZ]

Not working for some reason.
Copy and paste from fx box: =15000-SUM(B3:B10)

[Yisroel Tech]

Not working for some reason.
Copy and paste from fx box: =15000-SUM(B3:B10)

Works fine for me. Can you upload a sample Excel file?

[JTZ]

Can I just attach a file?

[etech0]

Works fine for me.

+1

What results do you get?

[aygart]

Can I just attach a file?

Do it in a new sheet. There is something hidden there.

[aygart]

You had the formula in that cell 3 times including with spaces in the middle

[JTZ]

 
Thank you! At least I tried before asking. 

[Zalc]

I may have got it.
I'll elaborate in a bit

I was running into a error, trying to calculate a Sum Total that includes a percentage of itself as one of the items.

The simplest solution was to go to Options > Formulas > Enable Iterative Calculations.

You need to remember to disable it after though.

[churnbabychurn]

I have a couple of 100k rows of data that I am pivoting. My PC/Excel is just about handling it.. crashes etc.

Does saving the data as CSV, and using a separate Excel WB to pivot and analyse​ make things better of worse?

[CZ60]

I have a couple of 100k rows of data that I am pivoting. My PC/Excel is just about handling it.. crashes etc.

Does saving the data as CSV, and using a separate Excel WB to pivot and analyse​ make things better of worse?

No difference imho. You're better off in access and linking your excel to the access DB.

[churnbabychurn]

No difference imho. You're better off in access and linking your excel to the access DB.

Access uses less memory?

[CZ60]

Access uses less memory?

Can handle a lot more rows of data, up to 2gb, and won't crash. You can connect to an external data source in Excel and have it look at the db file.

[Mikeoracle]

You can also try checking the box "add data to model" in the pivot wizard, that may help with processing large amounts of data.

[stooges44]

If I have this in a cell: 6:55 - 7:00 PM , what formula can I use to give me: 7:00 - 7:05 PM in the adjacent cell?

Thanks

[lubaby]

If I have this in a cell: 6:55 - 7:00 PM , what formula can I use to give me: 7:00 - 7:05 PM in the adjacent cell?

Thanks

This will add 5 minutes to a Time cell.

Code: [Select]

=A1+5/1440


But, being that you have 2 Times in 1 cell (probably formatted as General or Text) you'll need to re-build that part of your workbook to make it work.

[stooges44]

This will add 5 minutes to a Time cell.

Code: [Select]

=A1+5/1440


But, being that you have 2 Times in 1 cell (probably formatted as General or Text) you'll need to re-build that part of your workbook to make it work.

I know, I can do =A1+TIME(0,5,0) to also give me 5 minutes but like you said I have 2 times in a cell and I was hoping to get one large formula for everything.

[lubaby]

I know, I can do =A1+TIME(0,5,0) to also give me 5 minutes but like you said I have 2 times in a cell and I was hoping to get one large formula for everything.

Don't think that's possible. Good luck!

[etech0]

it would be much easier if you could split the times into 2 cells

[stooges44]

it would be much easier if you could split the times into 2 cells

Of course, but that would be the as much work as not using a formula 

I've gotten it theory but it gives the result in decimal format, not as H:MM PM

In cell L13 I have 6:55 - 7:00 PM, and I managed to make this formula

Code: [Select]

=CONCAT(LEFT(L13, SEARCH(" ",L13,1))+TIME(0,5,0)," - ", RIGHT(L13, SEARCH(" ",L13,1)+2)+TIME(0,5,0))
which essentially finds the 2 times, adds 5 to each and then joins them with the "-" but the result is in decimal format like this: 0.291666666666667 - 0.795138888888889 even after I change the cell type to time it has no affect

[CreamofSoup]

Try:

Code: [Select]

=CONCAT(text(LEFT(L13, SEARCH(" ",L13,1))+TIME(0,5,0),"H:MM AM/PM")," - ", text(RIGHT(L13, SEARCH(" ",L13,1)+2)+TIME(0,5,0),"H:MM AM/PM"))
The Text function before the left and right (and the H:MM AM/PM) formatting after the decimal number will force the decimal to the time that it represents, I think

Of course, but that would be the as much work as not using a formula 

I've gotten it theory but it gives the result in decimal format, not as H:MM PM

In cell L13 I have 6:55 - 7:00 PM, and I managed to make this formula

Code: [Select]

=CONCAT(LEFT(L13, SEARCH(" ",L13,1))+TIME(0,5,0)," - ", RIGHT(L13, SEARCH(" ",L13,1)+2)+TIME(0,5,0))
which essentially finds the 2 times, adds 5 to each and then joins them with the "-" but the result is in decimal format like this: 0.291666666666667 - 0.795138888888889 even after I change the cell type to time it has no affect

Wasn't sure I had everything in the right place but this will work.

Code: [Select]

=CONCATENATE(TEXT(LEFT(L13, SEARCH(" ",L13,1))+TIME(0,5,0),"H:MM")," - ", TEXT(RIGHT(L13, SEARCH(" ",L13,1)+2)+TIME(0,5,0),"H:MM AM/PM"))