Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Free Games gifts

How to calculate that the game gets the buyer a gift depending on the number of goods purchased in the shopping mall

Using Excel very quickly, we can calculate that the game gets a buyer, if in market store purchases of goods for a certain amount of money

In this example tutorial, I will show how in Excel we can perform the calculation that the game gets a buyer once it exceeds a certain financial amount of the invoice purchase goods at a merchant house.
In the example below, you see in column 'G' limited amounts provided that the buyer received as a gift a free game. Column 'H' you notice the names of games for PC and mobile phones that department store gives free of charge if the customer exceeds the limited amount of purchased goods.

Names of free games as gifts are:
  • Hello Kitty game
  • Barbie game
  • Commando 2 game
In column 'A' note the names of customers (buyers)
In column 'B' note the number of goods purchased. These goods can be not just one product but many different products
In column 'C' note the total amount of goods purchased
In column 'D' notice games that the customer has received the gift if the transferred amount of money saved, this column is solved by using the IF function.
In column 'D' notice games that the customer has received the gift if, over a certain amount of money, this column is solved by using the VLOOKUP function that is nested in an IF function or formula.


A
B
C
D
E
1
buyer
quantity
sum amount
gift (formula IF)
gift (formula Vlookup)
2
A
1
$320
3
B
1
$245
4
C
5
$630Barbie gameBarbie game
5
D
1
$289
6
E
1
$540
7
F
1
$620
8
G
1
$570
9
H
4
$620Hello Kitty gameHello Kitty game
10
I
1
$320
11
J
1
$620
12
K
8
$620Commando 2 gameCommando 2 game
13
L
2
$850Hello Kitty gameHello Kitty game
14
M
2
$480
15
N
1
$350
16
O
2
$120
17
P
8
$360Barbie gameBarbie game
18
19
$ amount
gift
20
$1,000
Hello Kitty game
21
$2,500
Barbie game
22
$4,000
Commando 2 game

Game gift - free game

The first way of dealing with free gifts games

The formula in column D solves multiple conditions and the following: Note that you have used three IF functions that are nested because we have three conditions (game gift)

=IF(B2*C2>$G$4;$H$4;IF(B2*C2>$G$3;$H$3;IF(B2*C2>$G$2;$H$2;"")))

Another way of dealing with free gifts games

The formula in column 'D'. You notice that was used VLOOKUP function that is nested in IF function/formula. Cells range D20:E22 in this case the Define Name 'gifts'.

=IF(B2*C2<1000;"";VLOOKUP(B2*C2;gifts;2))

You can set formula in ROW2 and copy down