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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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,049
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,732
Messages
5,524,516
Members
409,583
Latest member
gkarthick

This Week's Hot Topics

Top