Calculate Figure Based off 3 Values : Complex Formula?

Good morning!

I'm trying to create a formula using 3 values. All of the values are between 1 and 100. For the purposes of this we'll call the values E, I and A and the value we want to calculate C. I'm trying to write a "simple" formula to do this, but I'm struggling a bit. The logic for the result is:

C= (Ix8)+(Ax4)
C= round (C/4)

If (C<250) then C=round ((C/250)x100)
If (C>250) then C=round ((250/C)x100)
If (C=250) then C= 100

If (E<11) then C=round (C-(C/10))
If (E<21) then C=round (C-(C/10))
If (E<31) then C=round (C-(C/10))

I'm sure the logic in this calculation can be simplified, and the E<11/E<21/E<31 calculation is performed 3 times, so if the value of E is 5 then it's less than 11, less than 21 and less than 31 so is subjected to 3 calcuations of C=round (C-(C/10)).

Sorry for a smelly one on a Sunday morning!

Hi,

Since E<11, E<21, E<31, all of them refer to same thing, that is C=round (C-(C/10)), so you can simply discard these two lines :
If (E<11) then C=round (C-(C/10))
If (E<21) then C=round (C-(C/10))

You only need the latest line :
If (E<31) then C=round (C-(C/10))

Because (E<11) is covered in (E<31), so the (E<21).
Don't you think so ?

Hi,

I find this confusing, Not because I don't understand the formulae you show but because you seem to be implying a circular reference in C i.e. C=Round(C-C/10).
If that is indeed the case then you'll probably need a macro.

However upload a workbook and manually enter the results you expect to see for various initial data values. Tell us which cells are data and which are results.

This is the single cell formula (put on anywhere on row 2 outside data range) :

=ROUND((ROUND(((B2*8)+(C2*4))/4,0)/250)^SIGN(250-ROUND(((B2*8)+(C2*4))/4,0))*100,0)*0.9^LOOKUP(A2,{0,3;11,2;21,1})

Steps to reproduce this formula :


E2 =(B2*8)+(C2*4)


F2 =ROUND(E2/4,0)
F2 =ROUND(((B2*8)+(C2*4))/4,0)


Your if() function in G2 can be replaced to non if() formula like this :
G2 =ROUND((F2/250)^SIGN(250-F2)*100,0)
G2 =ROUND((ROUND(((B2*8)+(C2*4))/4,0)/250)^SIGN(250-ROUND(((B2*8)+(C2*4))/4,0))*100,0)


Then you need to subtract by 10% several times based on how often the value of A2 less than some conditional values. A number, subtracted by 10% is equal to 90% the number, in other words =number * 0.9

If A2 < 11 --> one time subtraction --> Number * 0.9 = Number * 0.9^1
If A2 < 21 --> two times subtraction --> (Number * 0.9) * 0.9 = Number * 0.9^2
If A2 < 31 --> three times subtraction --> ((Number * 0.9) * 0.9) * 0.9 = Number * 0.9^3

so it can be simplified as Number * 0.9^occurence

To find occurence, use lookup() function like this :
=LOOKUP(A2,{0,3;11,2;21,1})


So the final formula :
=G2 * 0.9 ^ occurence
=ROUND((ROUND(((B2*8)+(C2*4))/4,0)/250)^SIGN(250-ROUND(((B2*8)+(C2*4))/4,0))*100,0)*0.9^LOOKUP(A2,{0,3;11,2;21,1})

Glad I can help