Formula function by date

Hello all, I need some help. In this test excel I have a formula to read odds color(Green and Red)

Function CheckColor(range)
    If range.Interior.Color = RGB(256, 0, 0) Then
        CheckColor = "Red"
    ElseIf range.Interior.Color = RGB(0, 176, 80) Then
        CheckColor = "Green"
    Else
        CheckColor = "Neither"
    End If
End Function

I also find function to read the quantity of each day
24 Feb is 3
25 Feb is 1
etc

Now I want some more difficult function.

For the 3 odds of 24 Feb (3.58 red , 1.58 red , 2.77 green)
starting from 0

-6 for each red

(cell value-1)*0.97 for each green

So I want 0 -6 -6 +(2.77-1)*0.97 = -10.28


For 1 odds of 25 Feb (15.12 red)
starting from 0
0-6 = -6

For 4 odds of 26 Feb (7.00 green , 1.80 red , 3.56 green , 2.78 green
starting from 0
0 +(7.00-1)*0.97 -6 +(3.56-1)* 0.97 +(2.78-1)*0.97 = 4.03

etc

I can imagine that I need =IF(A1:A18 ; "*"& 24 Feb &"*" ; ???????)