Lost on writing Excel formula

E

EchoDelta

New member
Joined
Feb 28, 2015
Messages
16
Reaction score
0
Points
1
Excel Version(s)
2013
I would like to have a formula that if a number in a cell “G16” is greater than the sum of two cells “G5 & G13” plus 2, then the number in another cell “P16” is reduced by the amount that the number calculated is greater than the original number "G16". If the original number does not exceed the calculation then there will be no deduction.

I would then like for this formula to be repeated in an additional eight cells “H16 through O16” but the cell that is being reduced “P16” remains the same.

Hopefully this makes sense.

Thanks kindly
 
You cannot change the value of a cell already containing a value (P16) with a formula. A cell can contain EITHER a value OR a formula, but not both. If I understand you correctly, this would need VBA and cannot be done with a formula.
 
Ali - Thank you for your response. I guess I need to clarify my question.

I am putting a formula in Cell Q16 that determines how much P16 needs to be reduced (if any) based on the amount determined by how much G16 is greater than (G5+G13+2). This calculation can be either 0, 1 or 2.

Cell P16 =SUM(G16:O16)

Cell Q16 =IF(G16<(G5+G13+2),($P16-2),$P16)

The formula which I currently have in Q16 is not correct. The statement ($P16-2) is where my problem is. This part of the formula needs to be P16 minus the amount that is calculated. This calculation can be zero through three. This number also need to be cumulative G16 through O16.

Although this has no impact on what I am trying to achieve, the number in G5 can vary from 3 to 5 and the number in G13 is pulled from a calculation and can vary from 0 to 3.
 
A wild guess:
=G5+G13+2-G16
 
p45cal - That is the correct amount that P16 would be reduced for G16 but I need P16 to reflect the accumulation of G16 through O16.
 
Please attach a sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
 
p45cal - That is the correct amount that P16 would be reduced for G16 but I need P16 to reflect the accumulation of G16 through O1
 
Sample worksheet

1744562056854.png

Q16 is the cell which I am trying to calculate. The numbers in Row 5 are given. The numbers in Row 13 are from a calculation. The numbers in Row 16 are just random numbers to test the formula. The number in Q16 should actually be 45.
 
Maybe
Code: 
=P16-SUM(IF(G16:O16-(G5:O5+G13:O13+2)>0,G16:O16-(G5:O5+G13:O13+2),0))
?
In Excel 2013 it will probably need committing to the sheet using Ctrl+Shift+Enter rather than the usual plain Enter. This is called array-entering a formula.

Now if you're using a more recent version of Excel than is indicated by your user profile, come back, it could be easier.
 
p45cal - I am using Excel 2016. Using your formula I get the dreaded error #VALUE

Logit - your formula gives me a blank cell
 
You must log in or register to reply here.
Back
Top