Topic Wiki

For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by shoobi on November 12, 2015, 11:24:07 PM »

Author Topic: Excel Help and Problems  (Read 101216 times)

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7190
  • Total likes: 275
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1275 on: June 23, 2019, 12:55:41 PM »
Then it gives a little triangle in the top left of the cell indicating an error.
I thought Excel was powerful. It can't store a 20 digit number properly? That is sad.
Excel is for numbers, if you need to store text use word.

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9535
  • Total likes: 365
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1276 on: June 23, 2019, 02:25:27 PM »
Excel is for numbers, if you need to store text use word.
Or possibly even MS Access
Workflowy. You won't know what you're missing until you try it.

Offline CountValentine

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Mar 2013
  • Posts: 5000
  • Total likes: 466
  • DansDeals.com Hat Tips 0
  • Gender: Female
    • View Profile
  • Location: Poland - Exiled
  • Programs: DAOTYA, DDF Level 3, 5K Lounge
Re: Excel Help and Problems
« Reply #1277 on: June 23, 2019, 02:39:05 PM »
Access works great. Handles everything I throw at it. Unfortunately this script needs excel.
You're so far up Trump's a** you can see Giuliani's feet.  HT Baruch

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2834
  • Total likes: 168
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1278 on: June 23, 2019, 03:29:07 PM »
Access works great. Handles everything I throw at it. Unfortunately this script needs excel.
There are supposedly addons that get around the 15 digit limit, here's an example:

http://precisioncalc.com/xlprecision.html

Can't personally vouch for it though.

Offline US

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Aug 2017
  • Posts: 996
  • Total likes: 24
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Help and Problems
« Reply #1279 on: June 25, 2019, 01:43:25 PM »
Can someone please help me with SUMIF,

If cell A1 has any text, subtract cell A2 from A3,

I tried =SUMIF(K24:K24,"<>",Q24-V24) but keep on getting an error.

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9535
  • Total likes: 365
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1280 on: June 25, 2019, 01:45:07 PM »
Can someone please help me with SUMIF,

If cell A1 has any text, subtract cell A2 from A3,

I tried =SUMIF(K24:K24,"<>",Q24-V24) but keep on getting an error.
You don't need SUMIF for this. Try:

=IF(A1<>"",A3-A2,"")

This will keep the cell blank if A1 is blank. If you want something else you can change the "" at the end.
Workflowy. You won't know what you're missing until you try it.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2834
  • Total likes: 168
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1281 on: June 25, 2019, 03:10:43 PM »
You don't need SUMIF for this. Try:

=IF(A1<>"",A3-A2,"")

This will keep the cell blank if A1 is blank. If you want something else you can change the "" at the end.
Or use IFERROR(A3-A2,""). Saves 2 characters ;D.

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9535
  • Total likes: 365
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1282 on: June 25, 2019, 05:22:58 PM »
Or use IFERROR(A3-A2,""). Saves 2 characters ;D.
:)
Workflowy. You won't know what you're missing until you try it.

Offline US

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Aug 2017
  • Posts: 996
  • Total likes: 24
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Help and Problems
« Reply #1283 on: June 25, 2019, 07:53:54 PM »
Or use IFERROR(A3-A2,""). Saves 2 characters ;D.
You don't need SUMIF for this. Try:

=IF(A1<>"",A3-A2,"")

This will keep the cell blank if A1 is blank. If you want something else you can change the "" at the end.
Thanks!

Offline Centurion

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jun 2012
  • Posts: 1312
  • Total likes: 2
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York / Jerusalem
  • Programs: spg plat,avis pres ,hertz plat ,AAexcutime, delta plat,hilton gold, united
Re: Excel Help and Problems
« Reply #1284 on: July 04, 2019, 08:37:18 AM »
I have a list of caller id numbers that called in to a store last 90 days.
I also have a list of the best customers.

how do i find who is on the big list from the customers?
thank you
you can only make a first impression ONCE

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 1496
  • Total likes: 159
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Help and Problems
« Reply #1285 on: July 04, 2019, 08:40:42 AM »
I have a list of caller id numbers that called in to a store last 90 days.
I also have a list of the best customers.

how do i find who is on the big list from the customers?
thank you
From the question it sounds (correct me if I'm wrong) that you don't know very well Excel formulas, and you'd probably wanna see an example formula directly for your situation, but without a sample file from you (for us to see how the layout of the data is) that's not really possible.

(You'd do a VLOOKUP probably.)

Offline Centurion

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jun 2012
  • Posts: 1312
  • Total likes: 2
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York / Jerusalem
  • Programs: spg plat,avis pres ,hertz plat ,AAexcutime, delta plat,hilton gold, united
Re: Excel Help and Problems
« Reply #1286 on: July 04, 2019, 08:46:55 AM »
From the question it sounds (correct me if I'm wrong) that you don't know very well Excel formulas, and you'd probably wanna see an example formula directly for your situation, but without a sample file from you (for us to see how the layout of the data is) that's not really possible.

(You'd do a VLOOKUP probably.)

i tried exact,match,vlookup= i must be doing it wrong  example below thanx!

(i did duplicate values and it is finding internal ones and i don't need the ones that called once.)

7180340878   callerid
7180451626   callerid
7180562373   callerid
7180673121   callerid
7180783868   callerid
7180894616   callerid
7181005363   callerid
7181116111   callerid
7181226858   callerid
7181228407   callerid
7181240024   callerid
7181251641   callerid
7181263258   callerid
7181274874   callerid
7181286491   callerid
7181298108   callerid
7181309725   callerid
7181321342   callerid
7181332959   callerid
7181337606   callerid
7181344576   callerid
7181356192   callerid
7181367809   callerid
7181379426   callerid
7181391043   callerid
7181402660   callerid
7181414277   callerid
7181425894   callerid
7181437511   callerid
7181448353   callerid
7181449127   callerid
7181460744   callerid
7181472361   callerid
7181483978   callerid
7181495595   callerid
7181507212   callerid
7181518829   callerid
7181530446   callerid
7181542062   callerid
7181553679   callerid
7181559101   callerid
7181565296   callerid
7181576913   callerid
7181588530   callerid
7181600147   callerid
7181611764   callerid
7181623381   callerid
7181634997   callerid
7181646614   callerid
7181658231   callerid
7181669848   callerid
7181669848   callerid
7181681465   callerid
7181693082   callerid
7181704699   callerid
7181716315   callerid
7181727932   callerid
7181739549   callerid
7181751166   callerid
7181762783   callerid
7181774400   callerid
7181780596   callerid
7181786017   callerid
7181797634   callerid
7181809250   callerid
7181820867   callerid
7181832484   callerid
7181844101   callerid
7181855718   callerid
7181867335   callerid
7181878952   callerid
7181890569   callerid
7181891343   callerid
7181902185   callerid
7181913802   callerid
7181925419   callerid
7181937036   callerid
7181948653   callerid
7181960270   callerid
7181971887   callerid
7181983504   callerid
7181995120   callerid
7182002091   callerid
7182006737   callerid
7182018354   callerid
7182029971   callerid
7182041588   callerid
7182053205   callerid
7182064822   callerid
7182076438   callerid
7182088055   callerid
7182099672   callerid
7182111289   callerid
7182112838   callerid
7182122906   callerid
7182134523   callerid
7182146140   callerid
7182157757   callerid
7182169373   callerid
7182180990   callerid
7182192607   callerid
7182204224   callerid
7182215841   callerid
7182222222   callerid
7182222222   callerid
7182222222   callerid
7182222222   callerid
7182222222   callerid
7182223131   callerid
7182223131   callerid
7182223131   callerid
7182223131   callerid
7182223131   callerid
7182223131   callerid
7182223131   callerid
7182223131   callerid
7182223131   callerid
7182223131   callerid
7182223586   callerid
7182227458   callerid
7182239075   callerid
7182250692   callerid
7182262308   callerid
7182273925   callerid
7182285542   callerid
7182297159   callerid
7182308776   callerid
7182320393   callerid
7182332010   callerid
7182343626   callerid
7182355243   callerid
7182366860   callerid
7182378477   callerid
7182390094   callerid
7182401711   callerid
7182413328   callerid
7182424945   callerid
7183333333   callerid
7183333333   callerid
7183333333   callerid
7183333333   callerid
7183333333   callerid
   
   
   
   
7182222222   customers
7183333333   customers
7182223131   customers
you can only make a first impression ONCE

Offline Centurion

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jun 2012
  • Posts: 1312
  • Total likes: 2
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York / Jerusalem
  • Programs: spg plat,avis pres ,hertz plat ,AAexcutime, delta plat,hilton gold, united
Re: Excel Help and Problems
« Reply #1287 on: July 04, 2019, 08:58:24 AM »
=vlookup(A147:A149, A1:A142)
you can only make a first impression ONCE

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 1496
  • Total likes: 159
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Help and Problems
« Reply #1288 on: July 04, 2019, 09:17:11 AM »
In C147 you'd want =NOT(ISERROR(MATCH(A147,$A$1:$A$142,0))) to give a True or False if a customer called, and if you want to know how many times they did  - if they did - you can also do (in D147) =COUNTIF($A$1:$A$142,A147)

Good?

Offline aygart

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: May 2008
  • Posts: 7350
  • Total likes: 1941
  • DansDeals.com Hat Tips 7
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1289 on: July 04, 2019, 03:19:39 PM »
I am trying to track the quantities of 2 needed products contracted for various locations. Each location purchases separately and contracts for each month on its own. Various amounts are contracted at various times depending on a number of factors and some is left uncontracted until the time of use. I have a sheet which can track each location on its own and would like to be able to view a summary of the totals for all locations at once. Is there a formula or any other way to easily have this information update automatically on the summary sheet and be able to easily add locations and/or months? This would need the ability to be able to copy paste the pattern easily if a formula is used. I am open to changing the location sheet if that will make it easier. Sample attached.
You have the right to remain silent. Anything you say can and will be used to start a religious discussion.