Divide by VBA

[ashani]

Hi

I wonder if someone could help me with this please.

- If in Column G select "social" then increase allowance by 20% of K1 for that particular day
- Also reduced equally for all other days - however the total should remain 700 in Column C.

I'd really appreciate your guidance in the attachment.

thanks

 

[ashani]

Hi

Thank you for the reply.

I'm trying but not getting what I'm trying to achieve.

Thanks once again.

 

[StephenCrump]

I'm trying but not getting what I'm trying to achieve.

Where are you getting stuck? What have you tried that's not working?

 

[ashani]

When I’m trying to extend the formula to cover Training part and it’s not working as me come out with error message

 

[ashani]

I'm not sure what's going on - spent last 2 days just to merge 2 IFs function to get the result but just not happening.

All I'm trying to do is the following :

Social example -

- Monday Social = Give 20% of 700 is 140 plus existing 100 so total = 240 - yeah ?
- Tuesday to Sunday = 77 as (240 - 700 = 490 / 6)
- If Social again selected say on Thursday than it will divide the first social into 2 and rest of the cells will remain the same i.e. (240 + 77 = 317 /2 = 159 approx)
- If every day is a social than it will be 100 each day

Training example : exactly the same as Social with 10%

- Monday Training = Give 10% of 700 is 70 plus existing 100 so total = 170 - yeah ?
- Tuesday to Sunday = 88 or 89 as (140 - 700 = 560 / 6)
- If Training again selected say on Thursday than it will divide the first social into 2 and rest of the cells will remain the same i.e. (170 + 89 = 259 /2 = 130 approx)
- If every day is a training than it will be 100 each day.

However, if No Social or Training selected and Work selected than it will remain for whatever the numbers are and don't change anything.

I'd really appreciate if you could provide me some guidance on the above example.

Many thanks once again for all your help.

 

[ashani]

Do you think is that possible to do ?

 

[StephenCrump]

I'm not sure what's going on - spent last 2 days just to merge 2 IFs function to get the result but just not happening.

Do you think is that possible to do ?

Yes. It's easy to construct an IF() ... provided you are clear about the logic.

Based on your previous posts, it appears your "rules" are:

1. All days of the week are blank
Each day = 100

2. N days are SOCIAL, (7-N) days are blank
a) Total allocation for blank days = ROUND(N*(700-240)/6,0)
b) Total allocation for SOCIAL days = 700 - (a).
Each day rounded to an integer so that blank days total = (a) and SOCIAL days total = (b)

3. N days are TRAINING, (7-N) days are blank
Total allocation for blank days = ROUND(N*(700-170)/6,0)
Total allocation for TRAINING days = 700 - (a).
Each day rounded to an integer so that blank days total = (a) and TRAINING days total = (b)

Your screenshots then show a mix of SOCIAL, TRAINING, WORK and blank days, without explaining how you produce your results.

You'll need to codify as I have done above:
- the different calculations that may be made, and
- the conditions in which those calculations will apply.