Adding value in a cell

baalim

New Member
Joined
Feb 11, 2019
Messages
5
Hi
I kindly need an IF formula that when adding 100 to the cell it increases, example when I put value in B2 cell then B3 gets increased until reaching 100. Then in B4 a message tells “reaching the range”. The message will not show until B3 reaches 100 and so.
When B3 reaches 100, it still gets increased when putting more values in B2. Now, it reached 100 and I have the message shown. So I add 40 and later 5 in B2 for B3 to become 145, until reaching 200 then the message will show again and so on 300, 400, 500, 600..etc

Thanks,
Maher
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,424
Office Version
  1. 2019
Can only be done with VBA, not formulas.

Consider this:

You enter a value in B2.
It adds B2 to B3.
How does Excel know you've when entered another value in B2 (which could be the same value as before) ?

Only way is to enter a value, press a button to add, if B3 is a multiple of 100 repeat this.
This can only be done with VBA not formulas.

And you've asked for a formula to do this.
 
Last edited:

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,388
Office Version
  1. 2016
Platform
  1. Windows
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$2" Then Exit Sub
Application.EnableEvents = False
[B4].ClearContents
[B3] = [B3] + [B2]
If [B3] Mod 100 < [B2] Then [B4] = "reaching the range"
Application.EnableEvents = True
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,598
Messages
5,832,645
Members
430,150
Latest member
amitk1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top