Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-07-2021, 07:52 PM
Nasho23 Nasho23 is offline Formula to deal with Number and Text Fields. Windows 10 Formula to deal with Number and Text Fields. Office 2016
Novice
Formula to deal with Number and Text Fields.
 
Join Date: Feb 2021
Posts: 4
Nasho23 is on a distinguished road
Default Formula to deal with Number and Text Fields.

I need a Formula for this situation.


My Sheet is setup with these Columns - C for "DEL" & D for "ADD" and G for Count.


Lets Say, Column G starts with a Number (1000) in G1

If the 2nd Row Cell is C2 = DEL(-1), G2 in this Row needs to be G1 in the Row above, minus 1, making the New Count at G2, 1 less than the G1 in Row above G2 = 999.

If the 3rd Row has D3 = ADD (+1), G3 in this Row needs to be G2 in the Row above, plus 1, making the New Count G3 on this Row, 1 greater than G2 in the Row above.


C D G
1 1000
2 DEL 999
3 ADD 1000
So it goes on with the Col G changing, Up or Down, based on the Value in Cols C & D.
Can you please provide ma a Formula to achieve and that will Copy forwards in Col C.
I hope you can follow what I am doing to remove the Human Error in Adding or Subtracting in Col G.

Thank You
Reply With Quote
  #2  
Old 02-08-2021, 01:34 AM
ArviLaanemets ArviLaanemets is offline Formula to deal with Number and Text Fields. Windows 8 Formula to deal with Number and Text Fields. Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Why do you need 2 columns for "Del" and "ADD"?
When for every row you use only one, then those values can be in same column.
When you are using both of them in same row, then taking into account they add and delete 1, which results in no change at all, so you can left both of those sells empty with exactly same result.

And use tables for data entry - enter table header at top, and data into according rows below header row. This helps you to keep your data organized, and when you look at then after a couple of months later, you'll know what it is about.
An even more advanced approach is to define the data organized in this way as a Table. Then you can use Table syntax formulas, which are expanding automatically whenever you enter new data into defined Table.

Another advice is never enter any initializing values (1000 in your example) directly into table. This can mess up with your formulas, and this isn't obvious at first glance, from where this value is gotten.

Added is an example!
Attached Files
File Type: xlsx CountingTableExample.xlsx (12.6 KB, 7 views)
Reply With Quote
  #3  
Old 02-08-2021, 04:39 PM
Nasho23 Nasho23 is offline Formula to deal with Number and Text Fields. Windows 10 Formula to deal with Number and Text Fields. Office 2016
Novice
Formula to deal with Number and Text Fields.
 
Join Date: Feb 2021
Posts: 4
Nasho23 is on a distinguished road
Default

Hello Avril, Thank You for your Response. I am sorry - I should have attached my DBCount Sheet, so that my needs would be clear to you - It is now attached.


This is a Simple Support Sheet to my National Rats of Tobruk Database of 21,372 records, that are under constant adjustment. When Additions or Deletions of Members are made to my DB, I record them on this sheet, with other related information.
ADD means to ADD a New Member - DEL means to Remove a Member.
I need to Adjust the Column G (New Count) Automatically on entry, instead of Manually, for accuracy and removal of Human Error.
When complete, this File is sent to my Assistant in another State, to adjust his Parallel Copy of my Database.
ADD and DEL are only in different Columns, so that those Columns can be Individually Added at end of Month.
Can you Please provide a Formula to Auto Adjust "New Count" on entry.
Thank You.
Attached Files
File Type: xlsx DB RECORD COUNT(1).xlsx (15.7 KB, 6 views)
Reply With Quote
  #4  
Old 02-09-2021, 01:49 PM
Purfleet Purfleet is offline Formula to deal with Number and Text Fields. Windows 10 Formula to deal with Number and Text Fields. Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Can you just use an IF?

=IF(TRIM(C5)="DEL",J4-1,IF(TRIM(D5)="ADD",J4+1,J4+0))

You also need to tidy up the spreadsheet a bit as you have circular references, spaces after the text in Action and incorrect actions (d88)
Attached Files
File Type: xlsx Copy of DB RECORD COUNT(1)_Purfleet.xlsx (17.9 KB, 5 views)
Reply With Quote
  #5  
Old 02-09-2021, 05:55 PM
Nasho23 Nasho23 is offline Formula to deal with Number and Text Fields. Windows 10 Formula to deal with Number and Text Fields. Office 2016
Novice
Formula to deal with Number and Text Fields.
 
Join Date: Feb 2021
Posts: 4
Nasho23 is on a distinguished road
Default Formula to deal with Number and Text Fields

Hi Purfleet, Thank You for your response. Sorry to be a pain in the butt, but I am not good at Excel Formulae. I think you have misunderstood my requirements. Please refer to my Excel Sheet that was attached.



I need the Formula to provide a Progressive Total, starting from G88 (21,384) taking into account each Row, whether it is Col C (DEL = -1) or Col D (ADD = +1), when each Row is filled.



I have tested your Formula and it does not do this.


Regards and Thanks.
Reply With Quote
  #6  
Old 02-09-2021, 10:30 PM
Purfleet Purfleet is offline Formula to deal with Number and Text Fields. Windows 10 Formula to deal with Number and Text Fields. Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

I did the formula in column J so you can see it vs your numbers and compare, if i copy it over to G88 and adjust that is exactly what it does (excepting the issues detailed above)
Attached Files
File Type: xlsx DB RECORD COUNT(1)_Purfleet.xlsx (18.3 KB, 4 views)
Reply With Quote
  #7  
Old 02-09-2021, 11:34 PM
Nasho23 Nasho23 is offline Formula to deal with Number and Text Fields. Windows 10 Formula to deal with Number and Text Fields. Office 2016
Novice
Formula to deal with Number and Text Fields.
 
Join Date: Feb 2021
Posts: 4
Nasho23 is on a distinguished road
Default Formula to deal with Number and Text Fields

Hi Purflrrt, Thank You.
I can see now how you had it set up.
I now have it working as I want it to.
Regards
Owen Carlton.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Reliable way to convert text to number with a formula levraininjaneer Excel 8 12-22-2019 04:47 PM
Formula to populate column with text prefix then next number (ascending) SerenityNetworks Excel 4 02-05-2019 07:22 AM
VBA to deal with Open/Replace/Save as Text file .txt nouking Excel Programming 1 09-12-2017 12:00 AM
Number and Text Formula Trouble Shooting Haha88 Excel 1 12-06-2016 11:25 PM
Formula to deal with Number and Text Fields. Formula, Compare two cell's text and based on results output a number Offrddrver Excel 4 03-23-2016 11:32 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:27 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft