How to increment cell value with IF condtion?

[zakmuh]

Hi everyone!

I'd be greateful if anyone can help me on this complicated query. Maybe easy peasy for you

I'm a structural design engineer and I want to develop my own spreadsheet to calculate required area (mm^2) of reinforcement bars in concrete to avoid concrete cracking due to temperature rise in summer. Available input are min area of reinfor (cell A3), crack width limit (A1) and calculated crack with (A2). For example.....

A1= Crack width limit 0.2mm. A2= Calculated crack 0.28mm (which is higher than the limit 0.2mm; therefore min area of reinfor A3= 1450mm^2 is insufficient to stop concrete from cracking). Now......to stop cracking, I want cell B1 to take 1450 from A3 and keep increasing it until the condiotn A1>=A2 is met and I want cell B1 to display that increament( lets say 1780mm^2). I hope you do get what exaclty I want to do in excel

I don't have much experience in Excel so I don't know anything about Macro/VBA coding etc. Can we do a simple thing like this B1=IF(A1>=A2,A3,somthing here to keep adding a value to A3??) OR do we have to give pragamming code???

If it has to be Macro/VBA, could you please give me the complete coding with an instruction where to write it.

Cheers

NB: There are several calculations done with manual inputs within the sheet to derive the A2 (0.28mm) value

 

[Shrikant]

Hi everyone!

A1= Crack width limit 0.2mm. A2= Calculated crack 0.28mm (which is higher than the limit 0.2mm; therefore min area of reinfor A3= 1450mm^2 is insufficient to stop concrete from cracking). Now......to stop cracking, I want cell B1 to take 1450 from A3 and keep increasing it until the condiotn A1>=A2 is met and I want cell B1 to display that increament( lets say 1780mm^2). I hope you do get what exaclty I want to do in excel


NB: There are several calculations done with manual inputs within the sheet to derive the A2 (0.28mm) value

Is there any correlation between Calculated Crack (A2) , crack width limit and the value of A3 + Increment?

I mean by increasing the value of B1 by adding something in A3, will it affect A1 or A2?
If there is a mathematical model of this calculation , it is better if you can share that also.

 

[zakmuh]

Hello Shrikant,

Thanks for your time and trying to help me out on this mate and apologies for late reply, sorry!

NO, there isn't any correlation between A1 and A2 BUT there a correlation between A2 and B1. When the value in B1 (by adding something in A3) goes up, the value in A2 comes down. Basically, B1 should keep adding until the value in A3 becomes less than the value in A1.

Sorry I dont have a mathematical model.

Cheers

 

[zakmuh]

Hi,

I think with VB coding you have to run it to work? In that case Macro is the only way for me, so that it'd work itself?

Cheers

 

[Shrikant]

Hi.

Sorry, I am not sure if i have really got your problem.
Still from your description i have created a basic VBA code.

I have used the references that you have mentioned in this code so that you can modify it if needed.

Sub increment()
Dim CrackLimitA1 As Double
Dim CrackCalculatedA2 As Double
Dim BasevalueA3 As Double
Dim RequiredB1 As Double
Dim IncrementA4 As Double




'setting up values
CrackLimitA1 = Sheets("sheet1").Range("a1").Value
CrackCalculatedA2 = Sheets("sheet1").Range("a2").Value
BasevalueA3 = Sheets("sheet1").Range("a3").Value
IncrementA4 = Sheets("sheet1").Range("a4").Value


'setting base value for B1
RequiredB1 = BasevalueA3




Do Until CrackCalculatedA2 < RequiredB1


RequiredB1 = BasevalueA3 + IncrementA4
Sheets("sheet1").Range("B1").Value = RequiredB1
CrackLimitA1 = Sheets("sheet1").Range("a1").Value
CrackCalculatedA2 = Sheets("sheet1").Range("a2").Value


Loop


End Sub

 

[Shrikant]

I have added a new variable called Increment and you need to provide a value in A4 in your sheet.

Do not keep it blank or zero otherwise loop will never stop.

also do not keep it too small at first run else it will add more computational load.

what I suggest is you move step wise.

I mean

If A3 is 300 and if you estimate the required value (not necessary) to be around 500... then keep Increment as 25 for first run (I call this step as Pilot Run)

From Pilot Run, The required value comes at 550.

So now, to fine Tune , you can run the code second time.
This time you start with (A3 value) from say 490 and Keep Increment value to 1.

In this way if you need to run cycles to reach to desired level of accuracy.

 

[zakmuh]

Hi again Shrikant,

I tried your code and I've encountered two issues:
1. I have to click 'Run' for it to work each time?
2. When I click Run, rebar area value and calculated crack value change and then all of a sudden workbook freezes! Can't do anything but force closing.

So wot we know is....its working, well done Shrikant, but something is going wrong.

Can't we have any coding without to have to click Run each time?

Thanks!

NB: I tried to upload my excel file but couldn't fine a way to attach it!

 

[zakmuh]

Hi Shrikant,

The only way I could share the file is uploading to Box.com and heres the link for .xls file Im working with...

https://app.box.com/s/sgpt6pyjbn6dhxq2vq2b

Its 'Crack Control' sheet. Your coding reference A1=D22, A2=H72, A3=D30, A4= O75 and B1=D75

Cheers

 

[Shrikant]

Hi Zakmuh.

I knew there will be some issues.


It was a rough (Draft) code and i did not have any idea how you are using the data. That's why.
I will look into your file and will come back with refined code and solution to your problem as well.

 

[Shrikant]

Hi again Shrikant,

2. When I click Run, rebar area value and calculated crack value change and then all of a sudden workbook freezes! Can't do anything but force closing.

So wot we know is....its working, well done Shrikant, but something is going wrong.

For this particular problem i need some more information.

"It's working" means Are you reaching to your end result? Or You are able to run the code but it loops continuously?

This is important because based on this i ll have to modify few lines and looping method.

Anyways, I ll look the file and try to figure it out on my own.